﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using System.Text;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class ReceiptDetailLpnDA : IReceiptDetailLpnDA
    {
        private string ADJUSTED_QTY = "@ADJUSTED_QTY";
        private string ALT_SKU_ID = "@ALT_SKU_ID";
        private string CASE_CNT = "@CASE_CNT";
        private string CASE_ID = "@CASE_ID";
        private string CONDITION_CODE = "@CONDITION_CODE";
        private string CREATED_BY = "@CREATED_BY";
        private string CROSS_DOCK_ID = "@CROSS_DOCK_ID";
        private string CUBE = "@CUBE";
        private string DECLARATION_QTY = "@DECLARATION_QTY";
        private string DISPOSITION_CODE = "@DISPOSITION_CODE";
        private string DISPOSITION_TYPE = "@DISPOSITION_TYPE";
        private string EFFECTIVE_DATE = "@EFFECTIVE_DATE";
        private string EXPECTED_QTY = "@EXPECTED_QTY";
        private string EXPIRY_DATE = "@EXPIRY_DATE";
        private string EXTENDED_PRICE = "@EXTENDED_PRICE";
        private string EXTERN_LINE_ID = "@EXTERN_LINE_ID";
        private string EXTERN_RECEIPT_ID = "@EXTERN_RECEIPT_ID";
        private string EXTERNAL_LOT = "@EXTERNAL_LOT";
        private string GROSS_WGT = "@GROSS_WGT";
        private string HEIGHT = "@HEIGHT";
        private string INNER_PACK = "@INNER_PACK";
        private string LENGTH = "@LENGTH";
        private string LOT_ATTR01 = "@LOT_ATTR01";
        private string LOT_ATTR02 = "@LOT_ATTR02";
        private string LOT_ATTR03 = "@LOT_ATTR03";
        private string LOT_ATTR04 = "@LOT_ATTR04";
        private string LOT_ATTR05 = "@LOT_ATTR05";
        private string LOT_ATTR06 = "@LOT_ATTR06";
        private string LOT_ATTR07 = "@LOT_ATTR07";
        private string LOT_ATTR08 = "@LOT_ATTR08";
        private string LOT_ATTR09 = "@LOT_ATTR09";
        private string LOT_ATTR10 = "@LOT_ATTR10";
        private string LPN_ID = "@LPN_ID";
        private string MATCH_LOTTABLE = "@MATCH_LOTTABLE";
        private string NET_WGT = "@NET_WGT";
        private string OTHER_UNIT1 = "@OTHER_UNIT1";
        private string OTHER_UNIT2 = "@OTHER_UNIT2";
        private string OWNER_ID = "@OWNER_ID";
        private string PACK_ID = "@PACK_ID";
        private string PALLET = "@PALLET";
        private string PALLET_ID = "@PALLET_ID";
        private string PO_ID = "@PO_ID";
        private string PO_LINE_ID = "@PO_LINE_ID";
        private string PRODUCE_DATE = "@PRODUCE_DATE";
        private string QC_AUTO_ADJUST = "@QC_AUTO_ADJUST";
        private string QC_INSPECTED_QTY = "@QC_INSPECTED_QTY";
        private string QC_REJ_REASON = "@QC_REJ_REASON";
        private string QC_REJECTED_QTY = "@QC_REJECTED_QTY";
        private string QC_REQUIRED = "@QC_REQUIRED";
        private string QC_STATUS = "@QC_STATUS";
        private string QC_USER = "@QC_USER";
        private string REASON_CODE = "@REASON_CODE";
        private string RECEIPT_DATE = "@RECEIPT_DATE";
        private string RECEIPT_ID = "@RECEIPT_ID";
        private string RECEIPT_LINE_ID = "@RECEIPT_LINE_ID";
        private string RECEIVED_DATE = "@RECEIVED_DATE";
        private string RECEIVED_QTY = "@RECEIVED_QTY";
        private string REJECTED_QTY = "@REJECTED_QTY";
        private string REMARK = "@REMARK";
        private string RETURN_CONDITION = "@RETURN_CONDITION";
        private string RETURN_REASON = "@RETURN_REASON";
        private string RETURN_TYPE = "@RETURN_TYPE";
        private string RMA = "@RMA";
        private string SKU_ID = "@SKU_ID";
        private string SQL_CHECK_RECEIPT_DETAIL_LPN_ID_UNIQUE = " SELECT COUNT(1) FROM @_@RECEIPT_DETAIL_LPN WHERE RECEIPT_ID = @RECEIPT_ID AND RECEIPT_LINE_ID = @RECEIPT_LINE_ID AND LPN_ID = @LPN_ID ";
        private string SQL_DELETE_RECEIPT_DETAIL_LPN = " DELETE FROM @_@RECEIPT_DETAIL_LPN WHERE RECEIPT_ID = @RECEIPT_ID  ";
        private string SQL_GET_TOTAL_EXPECTED_QTY_AND_RECEIVED_QTY = "SELECT SUM(EXPECTED_QTY) AS TOTAL_EXPECTED_QTY,SUM(RECEIVED_QTY) AS TOTAL_RECEIVED_QTY FROM @_@RECEIPT_DETAIL_LPN WHERE RECEIPT_ID = @RECEIPT_ID";
        private string SQL_INSERT_RECEIPT_DETAIL_LPN = " INSERT INTO @_@RECEIPT_DETAIL_LPN ( WH_ID, RECEIPT_ID, RECEIPT_LINE_ID, LPN_ID, EXTERN_RECEIPT_ID, EXTERN_LINE_ID, OWNER_ID, PO_ID,PO_LINE_ID, TARIFF_ID, SKU_ID, ALT_SKU_ID, CASE_ID, STATUS, RECEIVED_DATE, EXPECTED_QTY, ADJUSTED_QTY, RECEIVED_QTY, REJECTED_QTY, UOM_ID, PACK_ID, TO_LOC, TO_LOT, TO_LPN_ID, CONDITION_CODE, LOT_ATTR01, LOT_ATTR02, LOT_ATTR03, LOT_ATTR04, LOT_ATTR05, LOT_ATTR06, LOT_ATTR07, LOT_ATTR08, LOT_ATTR09, LOT_ATTR10, PRODUCE_DATE, EXPIRY_DATE, RECEIPT_DATE, EXTERNAL_LOT, CASE_CNT, INNER_PACK, PALLET, CUBE, GROSS_WGT, NET_WGT, LENGTH, WIDTH, HEIGHT, OTHER_UNIT1, OTHER_UNIT2, UNIT_PRICE, EXTENDED_PRICE, EFFECTIVE_DATE, UDF1, UDF2, UDF3, UDF4, UDF5, UDF6, UDF7, UDF8, UDF9, UDF10, REASON_CODE, PALLET_ID, TYPE, RETURN_TYPE, RETURN_REASON, DISPOSITION_TYPE, DISPOSITION_CODE, RETURN_CONDITION, QC_REQUIRED, QC_INSPECTED_QTY, QC_REJECTED_QTY, QC_REJ_REASON, QC_STATUS, QC_USER, QC_AUTO_ADJUST, RMA, VENDOR_ID, VENDOR_NAME, MATCH_LOTTABLE, CROSS_DOCK_ID, REMARK, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE,DECLARATION_QTY ) VALUES ( @WH_ID, @RECEIPT_ID, @RECEIPT_LINE_ID, @LPN_ID, @EXTERN_RECEIPT_ID, @EXTERN_LINE_ID, @OWNER_ID, @PO_ID,@PO_LINE_ID, @TARIFF_ID, @SKU_ID, @ALT_SKU_ID, @CASE_ID, @STATUS, @RECEIVED_DATE, @EXPECTED_QTY, @ADJUSTED_QTY, @RECEIVED_QTY, @REJECTED_QTY, @UOM_ID, @PACK_ID, @TO_LOC, @TO_LOT, @TO_LPN_ID, @CONDITION_CODE, @LOT_ATTR01, @LOT_ATTR02, @LOT_ATTR03, @LOT_ATTR04, @LOT_ATTR05, @LOT_ATTR06, @LOT_ATTR07, @LOT_ATTR08, @LOT_ATTR09, @LOT_ATTR10, @PRODUCE_DATE, @EXPIRY_DATE, @RECEIPT_DATE, @EXTERNAL_LOT, @CASE_CNT, @INNER_PACK, @PALLET, @CUBE, @GROSS_WGT, @NET_WGT,  @LENGTH, @WIDTH, @HEIGHT,@OTHER_UNIT1, @OTHER_UNIT2, @UNIT_PRICE, @EXTENDED_PRICE, @EFFECTIVE_DATE, @UDF1, @UDF2, @UDF3, @UDF4, @UDF5, @UDF6, @UDF7, @UDF8, @UDF9, @UDF10, @REASON_CODE, @PALLET_ID, @TYPE, @RETURN_TYPE, @RETURN_REASON, @DISPOSITION_TYPE, @DISPOSITION_CODE, @RETURN_CONDITION, @QC_REQUIRED, @QC_INSPECTED_QTY, @QC_REJECTED_QTY, @QC_REJ_REASON, @QC_STATUS, @QC_USER, @QC_AUTO_ADJUST, @RMA, @VENDOR_ID, @VENDOR_NAME, @MATCH_LOTTABLE, @CROSS_DOCK_ID, @REMARK, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE(),@DECLARATION_QTY)  ";
        private string SQL_IS_COPY = " INSERT INTO @_@RECEIPT_DETAIL(WH_ID,RECEIPT_ID,RECEIPT_LINE_ID,EXTERN_RECEIPT_ID, EXTERN_LINE_ID, OWNER_ID, PO_ID, PO_LINE_ID, TARIFF_ID, SKU_ID, ALT_SKU_ID, LPN_ID, CASE_ID, STATUS, RECEIVED_DATE, EXPECTED_QTY, ADJUSTED_QTY, RECEIVED_QTY, REJECTED_QTY,UOM_ID, PACK_ID, TO_LOC, TO_LOT, TO_LPN_ID, CONDITION_CODE, LOT_ATTR01, LOT_ATTR02, LOT_ATTR03, LOT_ATTR04, LOT_ATTR05, LOT_ATTR06, LOT_ATTR07, LOT_ATTR08, LOT_ATTR09, LOT_ATTR10, PRODUCE_DATE, EXPIRY_DATE, RECEIPT_DATE, EXTERNAL_LOT, CASE_CNT, INNER_PACK, PALLET, CUBE, GROSS_WGT, NET_WGT,LENGTH , WIDTH , HEIGHT, OTHER_UNIT1, OTHER_UNIT2, UNIT_PRICE, EXTENDED_PRICE, EFFECTIVE_DATE, UDF1, UDF2, UDF3, UDF4, UDF5, UDF6, UDF7, UDF8, UDF9, UDF10, REASON_CODE, PALLET_ID, TYPE, RETURN_TYPE, RETURN_REASON, DISPOSITION_TYPE, DISPOSITION_CODE, RETURN_CONDITION, QC_REQUIRED, QC_INSPECTED_QTY, QC_REJECTED_QTY, QC_REJ_REASON, QC_STATUS, QC_USER, QC_AUTO_ADJUST, RMA, VENDOR_ID, VENDOR_NAME, MATCH_LOTTABLE, CROSS_DOCK_ID, REMARK,  CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE) SELECT WH_ID,'@RECEIPT_ID', RECEIPT_LINE_ID, EXTERN_RECEIPT_ID, EXTERN_LINE_ID, OWNER_ID, PO_ID, PO_LINE_ID, TARIFF_ID, SKU_ID, ALT_SKU_ID, LPN_ID, CASE_ID, 'New', RECEIVED_DATE, EXPECTED_QTY, 0, 0, 0,UOM_ID, PACK_ID, TO_LOC, TO_LOT, TO_LPN_ID, CONDITION_CODE, LOT_ATTR01, LOT_ATTR02, LOT_ATTR03, LOT_ATTR04, LOT_ATTR05, LOT_ATTR06, LOT_ATTR07, LOT_ATTR08, LOT_ATTR09, '@RECEIPT_ID', PRODUCE_DATE, EXPIRY_DATE, RECEIPT_DATE, EXTERNAL_LOT, CASE_CNT, INNER_PACK, PALLET, CUBE, GROSS_WGT, NET_WGT, LENGTH , WIDTH , HEIGHT, OTHER_UNIT1, OTHER_UNIT2, UNIT_PRICE, EXTENDED_PRICE, EFFECTIVE_DATE, UDF1, UDF2, UDF3, UDF4, UDF5, UDF6, UDF7, UDF8, UDF9, UDF10, REASON_CODE, PALLET_ID, TYPE, RETURN_TYPE, RETURN_REASON, DISPOSITION_TYPE, DISPOSITION_CODE, RETURN_CONDITION, QC_REQUIRED, QC_INSPECTED_QTY, QC_REJECTED_QTY, QC_REJ_REASON, QC_STATUS, QC_USER, QC_AUTO_ADJUST, RMA, VENDOR_ID, VENDOR_NAME, MATCH_LOTTABLE, CROSS_DOCK_ID, REMARK,  '@USER_ID', GETDATE(), '@USER_ID', GETDATE() FROM @_@RECEIPT_DETAIL_LPN WHERE RECEIPT_ID='@OLD_RECEIPT_ID'";
        private string SQL_SELECT_ALL_RECEIPT_DETAIL_LPN = " SELECT  RD.ROWID, RD.WH_ID, RD.RECEIPT_ID, RD.RECEIPT_LINE_ID, RD.EXTERN_RECEIPT_ID, RD.EXTERN_LINE_ID, RD.OWNER_ID, RD.PO_ID, RD.PO_LINE_ID, RD.TARIFF_ID, RD.SKU_ID, SKU.DESCR AS SKU_DESCR, RD.ALT_SKU_ID, RD.LPN_ID, RD.CASE_ID, RD.STATUS,T.[TEXT] AS STATUS_D, RD.RECEIVED_DATE, RD.EXPECTED_QTY, RD.ADJUSTED_QTY, RD.RECEIVED_QTY, RD.REJECTED_QTY, RD.UOM_ID, RD.PACK_ID, RD.TO_LOC, RD.TO_LOT, RD.TO_LPN_ID, RD.CONDITION_CODE, RD.LOT_ATTR01, RD.LOT_ATTR02, RD.LOT_ATTR03, RD.LOT_ATTR04, RD.LOT_ATTR05, RD.LOT_ATTR06, RD.LOT_ATTR07, RD.LOT_ATTR08, RD.LOT_ATTR09, RD.LOT_ATTR10, RD.PRODUCE_DATE, RD.EXPIRY_DATE, RD.RECEIPT_DATE, RD.EXTERNAL_LOT, RD.CASE_CNT, RD.INNER_PACK, RD.PALLET, RD.CUBE, RD.GROSS_WGT, RD.NET_WGT,RD.LENGTH,RD.WIDTH,RD.HEIGHT, RD.OTHER_UNIT1, RD.OTHER_UNIT2, RD.UNIT_PRICE, RD.EXTENDED_PRICE, RD.EFFECTIVE_DATE, RD.UDF1, RD.UDF2, RD.UDF3, RD.UDF4, RD.UDF5, RD.UDF6, RD.UDF7, RD.UDF8, RD.UDF9, RD.UDF10, RD.REASON_CODE, RD.PALLET_ID, RD.TYPE, RD.RETURN_TYPE, RD.RETURN_REASON, RD.DISPOSITION_TYPE, RD.DISPOSITION_CODE, RD.RETURN_CONDITION, RD.QC_REQUIRED, RD.QC_INSPECTED_QTY, RD.QC_REJECTED_QTY, RD.QC_REJ_REASON, RD.QC_STATUS, RD.QC_USER, RD.QC_AUTO_ADJUST, RD.RMA, RD.VENDOR_ID, RD.VENDOR_NAME, RD.MATCH_LOTTABLE, RD.CROSS_DOCK_ID, RD.REMARK, RD.TS, RD.CREATED_BY, RD.CREATED_DATE, RD.UPDATED_BY, RD.UPDATED_DATE,R.RECEIPT_TYPE,R.RMA,RD.DECLARATION_QTY,SKU.SKU_STYLE   FROM  @_@RECEIPT_DETAIL_LPN RD  LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = RD.STATUS AND SCD.SYS_CODE_TYPE = 'ReceiptStatus' LEFT JOIN sysadm.TRANSLATION T ON 'ReceiptStatus.'+RD.STATUS = T.ITEM_ID  \t\t\tAND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE LEFT JOIN @_@SKU SKU ON RD.OWNER_ID = SKU.OWNER_ID AND RD.SKU_ID = SKU.SKU_ID  LEFT JOIN @_@RECEIPT R ON RD.RECEIPT_ID = R.RECEIPT_ID  INNER JOIN SYSADM.USER_OWNER UO ON RD.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID ";
        private string SQL_SELECT_ALL_RECEIPT_DETAIL_LPN_BY_PAGER_QUERY = " SELECT  RD.ROWID, RD.WH_ID, RD.RECEIPT_ID, RD.RECEIPT_LINE_ID, RD.EXTERN_RECEIPT_ID, RD.EXTERN_LINE_ID, RD.OWNER_ID, RD.PO_ID, RD.PO_LINE_ID, RD.TARIFF_ID, RD.SKU_ID, SKU.DESCR AS SKU_DESCR, RD.ALT_SKU_ID, RD.LPN_ID, RD.CASE_ID, RD.STATUS,T.[TEXT] AS STATUS_D, RD.RECEIVED_DATE, RD.EXPECTED_QTY, RD.ADJUSTED_QTY, RD.RECEIVED_QTY, RD.REJECTED_QTY, RD.UOM_ID, RD.PACK_ID, RD.TO_LOC, RD.TO_LOT, RD.TO_LPN_ID, RD.CONDITION_CODE, RD.LOT_ATTR01, RD.LOT_ATTR02, RD.LOT_ATTR03, RD.LOT_ATTR04, RD.LOT_ATTR05, RD.LOT_ATTR06, RD.LOT_ATTR07, RD.LOT_ATTR08, RD.LOT_ATTR09, RD.LOT_ATTR10, RD.PRODUCE_DATE, RD.EXPIRY_DATE, RD.RECEIPT_DATE, RD.EXTERNAL_LOT, RD.CASE_CNT, RD.INNER_PACK, RD.PALLET, RD.CUBE, RD.GROSS_WGT, RD.NET_WGT, RD.LENGTH,RD.WIDTH,RD.HEIGHT, RD.OTHER_UNIT1, RD.OTHER_UNIT2, RD.UNIT_PRICE, RD.EXTENDED_PRICE, RD.EFFECTIVE_DATE, RD.UDF1, RD.UDF2, RD.UDF3, RD.UDF4, RD.UDF5, RD.UDF6, RD.UDF7, RD.UDF8, RD.UDF9, RD.UDF10, RD.REASON_CODE, RD.PALLET_ID, RD.TYPE, RD.RETURN_TYPE, RD.RETURN_REASON, RD.DISPOSITION_TYPE, RD.DISPOSITION_CODE, RD.RETURN_CONDITION, RD.QC_REQUIRED, RD.QC_INSPECTED_QTY, RD.QC_REJECTED_QTY, RD.QC_REJ_REASON, RD.QC_STATUS, RD.QC_USER, RD.QC_AUTO_ADJUST, RD.RMA, RD.VENDOR_ID, RD.VENDOR_NAME, RD.MATCH_LOTTABLE, RD.CROSS_DOCK_ID, RD.REMARK, RD.TS, RD.CREATED_BY, RD.CREATED_DATE, RD.UPDATED_BY, RD.UPDATED_DATE,R.RECEIPT_TYPE,R.RMA,   CASE WHEN TD2.DOC_ID IS NOT NULL THEN 'Y' ELSE 'N' END AS IS_GEN_PUTAWAY,SKU.SKU_STYLE  FROM  @_@RECEIPT_DETAIL_LPN RD  LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = RD.STATUS AND SCD.SYS_CODE_TYPE = 'ReceiptStatus' LEFT JOIN sysadm.TRANSLATION T ON 'ReceiptStatus.'+RD.STATUS = T.ITEM_ID  \t\t\tAND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE LEFT JOIN @_@SKU SKU ON RD.OWNER_ID = SKU.OWNER_ID AND RD.SKU_ID = SKU.SKU_ID  LEFT JOIN @_@RECEIPT R ON RD.RECEIPT_ID = R.RECEIPT_ID  LEFT JOIN (SELECT DISTINCT DOC_ID,DOC_LINE_ID FROM @_@TASK_DETAIL  WHERE DOC_ID = @RECEIPT_ID  AND TASK_TYPE = 'PA') TD2 ON RD.RECEIPT_ID = TD2.DOC_ID AND RD.RECEIPT_LINE_ID = TD2.DOC_LINE_ID INNER JOIN SYSADM.USER_OWNER UO ON RD.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID ";
        private string SQL_SELECT_ALL_RECEIPT_HISTORY = " SELECT IT.TRANS_ID,IT.RECEIPT_ID,IT.RECEIPT_LINE_ID,IT.SKU_ID,IT.OWNER_ID,RD.VENDOR_ID,IT.PACK_ID,IT.UOM_ID, IT.TO_LPN_ID,IT.LOT,IT.TO_LOC,IT.QTY,LAC.DESCR AS HOLD_CODE,SUM(ISNULL(TD.QTY,0)) AS COMPLETED_QTY, IT.QTY-SUM(ISNULL(TD.QTY,0))-SUM(ISNULL(PD.QTY,0)) AS REMAIN_QTY, SUM(ISNULL(PD.QTY,0)) AS ALLOCATED_QTY FROM @_@INV_TRANS IT  LEFT JOIN @_@RECEIPT_DETAIL_LPN RD ON IT.RECEIPT_ID = RD.RECEIPT_ID AND IT.RECEIPT_LINE_ID = RD.RECEIPT_LINE_ID  LEFT JOIN @_@TASK_DETAIL TD ON IT.TRANS_ID = TD.FROM_TRANS_ID AND TD.STATUS = 'Completed' LEFT JOIN @_@LOT_ATTRIBUTE LA ON IT.LOT = LA.LOT AND LA.LOT_ATTR10 = @RECEIPT_ID LEFT JOIN @_@LOT_ATTRIBUTE_CODE LAC ON LA.LOT_ATTR09 = LAC.CODE_ID AND LAC.GROUP_ID = '货品属性' LEFT JOIN @_@PICK_DETAIL PD ON IT.LOT = PD.LOT AND IT.TO_LOC = PD.LOC AND IT.TO_LPN_ID = PD.LPN_ID AND PD.STATUS IN ('NEW','RELEASE','PICKED','SHIPPED')  @CONDITION@ GROUP BY IT.TRANS_ID,IT.RECEIPT_ID,IT.RECEIPT_LINE_ID,IT.SKU_ID,IT.OWNER_ID,RD.VENDOR_ID,IT.PACK_ID,IT.UOM_ID, IT.TO_LPN_ID,IT.LOT,IT.TO_LOC,IT.QTY,LAC.DESCR,TD.STATUS ";
        private string SQL_SELECT_ALL_RECEIPT_HISTORY_LOT = " SELECT IT.TRANS_ID,IT.RECEIPT_ID,IT.RECEIPT_LINE_ID,IT.SKU_ID,IT.OWNER_ID,RD.VENDOR_ID,IT.PACK_ID,IT.UOM_ID, IT.TO_LPN_ID,IT.LOT,IT.TO_LOC,IT.QTY,LAC.DESCR AS HOLD_CODE,SUM(ISNULL(TD.QTY,0)) AS COMPLETED_QTY, IT.QTY-SUM(ISNULL(TD.QTY,0))-SUM(ISNULL(PD.QTY,0)) AS REMAIN_QTY, SUM(ISNULL(PD.QTY,0)) AS ALLOCATED_QTY,LA.LOT_ATTR01,LA.LOT_ATTR02,LA.LOT_ATTR03,LA.LOT_ATTR04,LA.LOT_ATTR05,LA.LOT_ATTR06,LA.LOT_ATTR07,LA.LOT_ATTR08,LA.LOT_ATTR09,LA.LOT_ATTR10,LA.PRODUCE_DATE,LA.EXPIRY_DATE,LA.RECEIPT_DATE,LA.EXTERNAL_LOT,S.DESCR FROM @_@INV_TRANS IT  LEFT JOIN @_@RECEIPT_DETAIL RD ON IT.RECEIPT_ID = RD.RECEIPT_ID AND IT.RECEIPT_LINE_ID = RD.RECEIPT_LINE_ID LEFT JOIN @_@SKU S ON IT.SKU_ID= S.SKU_ID AND IT.OWNER_ID = S.OWNER_ID LEFT JOIN @_@TASK_DETAIL TD ON IT.TRANS_ID = TD.FROM_TRANS_ID AND TD.STATUS = 'Completed' LEFT JOIN @_@LOT_ATTRIBUTE LA ON IT.LOT = LA.LOT AND LA.LOT_ATTR10 = @RECEIPT_ID LEFT JOIN @_@LOT_ATTRIBUTE_CODE LAC ON LA.LOT_ATTR09 = LAC.CODE_ID AND LAC.GROUP_ID = '货品属性' LEFT JOIN @_@PICK_DETAIL PD ON IT.LOT = PD.LOT AND IT.TO_LOC = PD.LOC AND IT.TO_LPN_ID = PD.LPN_ID AND PD.STATUS IN ('NEW','RELEASE','PICKED','SHIPPED')  @CONDITION@ GROUP BY IT.TRANS_ID,IT.RECEIPT_ID,IT.RECEIPT_LINE_ID,IT.SKU_ID,IT.OWNER_ID,RD.VENDOR_ID,IT.PACK_ID,IT.UOM_ID, IT.TO_LPN_ID,IT.LOT,IT.TO_LOC,IT.QTY,LAC.DESCR,TD.STATUS,LA.LOT_ATTR01,LA.LOT_ATTR02,LA.LOT_ATTR03,LA.LOT_ATTR04,LA.LOT_ATTR05,LA.LOT_ATTR06,LA.LOT_ATTR07,LA.LOT_ATTR08,LA.LOT_ATTR09,LA.LOT_ATTR10,LA.PRODUCE_DATE,LA.EXPIRY_DATE,LA.RECEIPT_DATE,LA.EXTERNAL_LOT,S.DESCR ";
        private string SQL_SELECT_ALL_RECEIPT_HISTORY_LOT_BY_PAGER_QUERY = " SELECT CASE WHEN IT.QTY = (SUM(ISNULL(TD.QTY,0))+SUM(ISNULL(PD.QTY,0)))   THEN  'Completed' ELSE 'Pending' END as STATUS , CASE WHEN IT.QTY = (SUM(ISNULL(TD.QTY,0))+SUM(ISNULL(PD.QTY,0)))   THEN CASE WHEN IT.QTY= SUM(ISNULL(PD.QTY,0)) THEN 'N'  ELSE 'Y' END ELSE 'N' END as IS_PUT_AWAYED, CASE WHEN TD2.DOC_ID IS NOT NULL THEN 'Y' ELSE 'N' END AS IS_GEN_PUTAWAY, IT.TRANS_ID,IT.RECEIPT_ID,IT.RECEIPT_LINE_ID,IT.SKU_ID,IT.OWNER_ID,RD.VENDOR_ID,IT.PACK_ID,IT.UOM_ID, IT.TO_LPN_ID,IT.LOT,IT.TO_LOC,IT.QTY AS RECEIVED_QTY,LAC.DESCR AS HOLD_CODE,SUM(ISNULL(TD.QTY,0)) AS COMPLETED_QTY, IT.QTY-SUM(ISNULL(TD.QTY,0))-SUM(ISNULL(PD.QTY,0)) AS REMAIN_QTY, SUM(ISNULL(PD.QTY,0)) AS ALLOCATED_QTY,LA.LOT_ATTR01,LA.LOT_ATTR02,LA.LOT_ATTR03,LA.LOT_ATTR04,LA.LOT_ATTR05,LA.LOT_ATTR06,LA.LOT_ATTR07,LA.LOT_ATTR08,LA.LOT_ATTR09,LA.LOT_ATTR10,LA.PRODUCE_DATE,LA.EXPIRY_DATE,LA.RECEIPT_DATE,LA.EXTERNAL_LOT,S.DESCR FROM @_@INV_TRANS IT  LEFT JOIN @_@RECEIPT_DETAIL_LPN RD ON IT.RECEIPT_ID = RD.RECEIPT_ID AND IT.RECEIPT_LINE_ID = RD.RECEIPT_LINE_ID  LEFT JOIN @_@SKU S ON IT.SKU_ID= S.SKU_ID AND IT.OWNER_ID = S.OWNER_ID LEFT JOIN @_@TASK_DETAIL TD ON IT.TRANS_ID = TD.FROM_TRANS_ID AND TD.STATUS = 'Completed' LEFT JOIN @_@LOT_ATTRIBUTE LA ON IT.LOT = LA.LOT AND LA.LOT_ATTR10 = @RECEIPT_ID LEFT JOIN @_@LOT_ATTRIBUTE_CODE LAC ON LA.LOT_ATTR09 = LAC.CODE_ID AND LAC.GROUP_ID = '货品属性' LEFT JOIN @_@PICK_DETAIL PD ON IT.LOT = PD.LOT AND IT.TO_LOC = PD.LOC AND IT.TO_LPN_ID = PD.LPN_ID AND PD.STATUS IN ('NEW','RELEASE','PICKED','SHIPPED')  LEFT JOIN (SELECT DISTINCT DOC_ID,DOC_LINE_ID FROM @_@TASK_DETAIL  WHERE DOC_ID =@RECEIPT_ID AND TASK_TYPE = 'PA') TD2 ON RD.RECEIPT_ID = TD2.DOC_ID AND RD.RECEIPT_LINE_ID = TD2.DOC_LINE_ID @CONDITION@ GROUP BY IT.TRANS_ID,IT.RECEIPT_ID,IT.RECEIPT_LINE_ID,IT.SKU_ID,IT.OWNER_ID,RD.VENDOR_ID,IT.PACK_ID,IT.UOM_ID, IT.TO_LPN_ID,IT.LOT,IT.TO_LOC,IT.QTY,LAC.DESCR,TD.STATUS,LA.LOT_ATTR01,LA.LOT_ATTR02,LA.LOT_ATTR03,LA.LOT_ATTR04,LA.LOT_ATTR05,LA.LOT_ATTR06,LA.LOT_ATTR07,LA.LOT_ATTR08,LA.LOT_ATTR09,LA.LOT_ATTR10,LA.PRODUCE_DATE,LA.EXPIRY_DATE,LA.RECEIPT_DATE,LA.EXTERNAL_LOT,S.DESCR,TD2.DOC_ID ";
        private string SQL_UPDATE_RECEIPT_DETAIL_FOR_RECEIGING = " UPDATE @_@RECEIPT_DETAIL_LPN SET RECEIVED_QTY = @RECEIVED_QTY, RECEIVED_DATE = @RECEIVED_DATE, RECEIPT_DATE = @RECEIPT_DATE, STATUS = @STATUS,LPN_ID = @LPN_ID,TO_LOC = @TO_LOC,TO_LOT = @TO_LOT,TO_LPN_ID = @TO_LPN_ID,UPDATED_BY = @UPDATED_BY,UPDATED_DATE = GETDATE() WHERE RECEIPT_ID = @RECEIPT_ID AND RECEIPT_LINE_ID = @RECEIPT_LINE_ID ";
        private string SQL_UPDATE_RECEIPT_DETAIL_LPN = " UPDATE @_@RECEIPT_DETAIL_LPN SET WH_ID = @WH_ID, EXTERN_RECEIPT_ID = @EXTERN_RECEIPT_ID, EXTERN_LINE_ID = @EXTERN_LINE_ID, OWNER_ID = @OWNER_ID, PO_ID = @PO_ID, PO_LINE_ID = @PO_LINE_ID, TARIFF_ID = @TARIFF_ID, SKU_ID = @SKU_ID, ALT_SKU_ID = @ALT_SKU_ID, CASE_ID = @CASE_ID, STATUS = @STATUS, RECEIVED_DATE = @RECEIVED_DATE, EXPECTED_QTY = @EXPECTED_QTY, ADJUSTED_QTY = @ADJUSTED_QTY, RECEIVED_QTY = @RECEIVED_QTY, REJECTED_QTY = @REJECTED_QTY, UOM_ID = @UOM_ID, PACK_ID = @PACK_ID, TO_LOC = @TO_LOC, TO_LOT = @TO_LOT, TO_LPN_ID = @TO_LPN_ID, CONDITION_CODE = @CONDITION_CODE, LOT_ATTR01 = @LOT_ATTR01, LOT_ATTR02 = @LOT_ATTR02, LOT_ATTR03 = @LOT_ATTR03, LOT_ATTR04 = @LOT_ATTR04, LOT_ATTR05 = @LOT_ATTR05, LOT_ATTR06 = @LOT_ATTR06, LOT_ATTR07 = @LOT_ATTR07, LOT_ATTR08 = @LOT_ATTR08, LOT_ATTR09 = @LOT_ATTR09, LOT_ATTR10 = @LOT_ATTR10, PRODUCE_DATE = @PRODUCE_DATE, EXPIRY_DATE = @EXPIRY_DATE, RECEIPT_DATE = @RECEIPT_DATE, EXTERNAL_LOT = @EXTERNAL_LOT, CASE_CNT = @CASE_CNT, INNER_PACK = @INNER_PACK, PALLET = @PALLET, CUBE = @CUBE, GROSS_WGT = @GROSS_WGT, NET_WGT = @NET_WGT,  LENGTH = @LENGTH, WIDTH = @WIDTH, HEIGHT = @HEIGHT, OTHER_UNIT1 = @OTHER_UNIT1, OTHER_UNIT2 = @OTHER_UNIT2, UNIT_PRICE = @UNIT_PRICE, EXTENDED_PRICE = @EXTENDED_PRICE, EFFECTIVE_DATE = @EFFECTIVE_DATE, UDF1 = @UDF1, UDF2 = @UDF2, UDF3 = @UDF3, UDF4 = @UDF4, UDF5 = @UDF5, UDF6 = @UDF6, UDF7 = @UDF7, UDF8 = @UDF8, UDF9 = @UDF9, UDF10 = @UDF10, REASON_CODE = @REASON_CODE, PALLET_ID = @PALLET_ID, TYPE = @TYPE, RETURN_TYPE = @RETURN_TYPE, RETURN_REASON = @RETURN_REASON, DISPOSITION_TYPE = @DISPOSITION_TYPE, DISPOSITION_CODE = @DISPOSITION_CODE, RETURN_CONDITION = @RETURN_CONDITION, QC_REQUIRED = @QC_REQUIRED, QC_INSPECTED_QTY = @QC_INSPECTED_QTY, QC_REJECTED_QTY = @QC_REJECTED_QTY, QC_REJ_REASON = @QC_REJ_REASON, QC_STATUS = @QC_STATUS, QC_USER = @QC_USER, QC_AUTO_ADJUST = @QC_AUTO_ADJUST, RMA = @RMA, VENDOR_ID = @VENDOR_ID, VENDOR_NAME = @VENDOR_NAME, MATCH_LOTTABLE = @MATCH_LOTTABLE, CROSS_DOCK_ID = @CROSS_DOCK_ID, REMARK = @REMARK, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE(),DECLARATION_QTY=@DECLARATION_QTY WHERE RECEIPT_ID = @RECEIPT_ID AND RECEIPT_LINE_ID = @RECEIPT_LINE_ID AND LPN_ID = @LPN_ID  ";
        private string SQL_UPDATE_RECEIPT_DETAIL_LPN_STAUTS = " UPDATE @_@RECEIPT_DETAIL_LPN SET STATUS = @STATUS, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE RECEIPT_ID = @RECEIPT_ID ";
        private string SQL_UPDATE_RECEIPT_DETAIL_PRICE = " UPDATE @_@RECEIPT_DETAIL_LPN SET UNIT_PRICE = @UNIT_PRICE,EXTENDED_PRICE=@EXTENDED_PRICE,UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE RECEIPT_ID = @RECEIPT_ID AND RECEIPT_LINE_ID=@RECEIPT_LINE_ID ";
        private string STATUS = "@STATUS";
        private string TARIFF_ID = "@TARIFF_ID";
        private string TO_LOC = "@TO_LOC";
        private string TO_LOT = "@TO_LOT";
        private string TO_LPN_ID = "@TO_LPN_ID";
        private string TS = "@TS";
        private string TYPE = "@TYPE";
        private string UDF1 = "@UDF1";
        private string UDF10 = "@UDF10";
        private string UDF2 = "@UDF2";
        private string UDF3 = "@UDF3";
        private string UDF4 = "@UDF4";
        private string UDF5 = "@UDF5";
        private string UDF6 = "@UDF6";
        private string UDF7 = "@UDF7";
        private string UDF8 = "@UDF8";
        private string UDF9 = "@UDF9";
        private string UNIT_PRICE = "@UNIT_PRICE";
        private string UOM_ID = "@UOM_ID";
        private string UPDATED_BY = "@UPDATED_BY";
        private string VENDOR_ID = "@VENDOR_ID";
        private string VENDOR_NAME = "@VENDOR_NAME";
        private string WH_ID = "@WH_ID";
        private string WIDTH = "@WIDTH";

        public int DeleteReceiptDetailLpn(DataBase dataBase, DbTransaction tran, List<string> receiptID, string whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; receiptID.Count > i; i++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", receiptID[i]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_RECEIPT_DETAIL_LPN.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int DeleteReceiptDetailLpn(DataBase dataBase, DbTransaction tran, string receiptID, string whLoginID)
        {
            int result = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", receiptID) };
                result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_RECEIPT_DETAIL_LPN.Replace("@_@", whLoginID), paras);
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public DataSet GetReceiptDetailByPagerQueryList(List<string> sqlWhere, ReceiptDetailQueryEntity receiptdetailQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_RECEIPT_DETAIL_LPN_BY_PAGER_QUERY.Replace("@_@", whLoginID) + " WHERE RD.RECEIPT_ID = @RECEIPT_ID";
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                temp = temp + " AND " + sqlWhere[i].ToString();
            }
            temp = PagingHelper.GetPagingSQL(temp, receiptdetailQuery.CurrentPage, receiptdetailQuery.PageSize, receiptdetailQuery.SortField, receiptdetailQuery.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''").Replace("@USER_ID", "''" + pubParasInfo.UserID + "''").Replace("@RECEIPT_ID", "''" + receiptdetailQuery.ReceiptID + "''");
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public ReceiptDetailInfo GetReceiptDetailByPoID(DataBase dataBase, DbTransaction tran, string poID, string poLineID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = string.Empty;
            sql = this.SQL_SELECT_ALL_RECEIPT_DETAIL_LPN.Replace("@_@", whLoginID) + " WHERE RD.PO_ID = @PO_ID AND RD.PO_LINE_ID = @PO_LINE_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@PO_ID", poID), new SqlParameter("@PO_LINE_ID", poLineID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            ReceiptDetailInfo receiptDetailInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                DateTime CS00003;
                receiptDetailInfo = new ReceiptDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["RECEIPT_ID"].ToString(), (reader["RECEIPT_LINE_ID"].ToString() != "") ? int.Parse(reader["RECEIPT_LINE_ID"].ToString()) : 0, reader["EXTERN_RECEIPT_ID"].ToString(), reader["EXTERN_LINE_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["PO_ID"].ToString(), int.Parse((reader["PO_LINE_ID"].ToString() == "") ? "0" : reader["PO_LINE_ID"].ToString()), reader["TARIFF_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["LPN_ID"].ToString(), reader["CASE_ID"].ToString(), reader["STATUS"].ToString(), (reader["RECEIVED_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIVED_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["EXPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["EXPECTED_QTY"].ToString()) : 0M, (reader["ADJUSTED_QTY"].ToString() != "") ? decimal.Parse(reader["ADJUSTED_QTY"].ToString()) : 0M, (reader["RECEIVED_QTY"].ToString() != "") ? decimal.Parse(reader["RECEIVED_QTY"].ToString()) : 0M, (reader["REJECTED_QTY"].ToString() != "") ? decimal.Parse(reader["REJECTED_QTY"].ToString()) : 0M, reader["UOM_ID"].ToString(), reader["PACK_ID"].ToString(), reader["TO_LOC"].ToString(), reader["TO_LOT"].ToString(), reader["TO_LPN_ID"].ToString(), reader["CONDITION_CODE"].ToString(), (reader["CASE_CNT"].ToString() != "") ? decimal.Parse(reader["CASE_CNT"].ToString()) : 0M, (reader["INNER_PACK"].ToString() != "") ? decimal.Parse(reader["INNER_PACK"].ToString()) : 0M, (reader["PALLET"].ToString() != "") ? decimal.Parse(reader["PALLET"].ToString()) : 0M, (reader["CUBE"].ToString() != "") ? float.Parse(reader["CUBE"].ToString()) : 0f, (reader["GROSS_WGT"].ToString() != "") ? float.Parse(reader["GROSS_WGT"].ToString()) : 0f, (reader["NET_WGT"].ToString() != "") ? float.Parse(reader["NET_WGT"].ToString()) : 0f, (reader["LENGTH"].ToString() != "") ? float.Parse(reader["LENGTH"].ToString()) : 0f, (reader["WIDTH"].ToString() != "") ? float.Parse(reader["WIDTH"].ToString()) : 0f, (reader["HEIGHT"].ToString() != "") ? float.Parse(reader["HEIGHT"].ToString()) : 0f, (reader["OTHER_UNIT1"].ToString() != "") ? float.Parse(reader["OTHER_UNIT1"].ToString()) : 0f, (reader["OTHER_UNIT2"].ToString() != "") ? float.Parse(reader["OTHER_UNIT2"].ToString()) : 0f, (reader["UNIT_PRICE"].ToString() != "") ? float.Parse(reader["UNIT_PRICE"].ToString()) : 0f, (reader["EXTENDED_PRICE"].ToString() != "") ? float.Parse(reader["EXTENDED_PRICE"].ToString()) : 0f, (reader["EFFECTIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["EFFECTIVE_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REASON_CODE"].ToString(), reader["PALLET_ID"].ToString(), reader["TYPE"].ToString(), reader["RETURN_TYPE"].ToString(), reader["RETURN_REASON"].ToString(), reader["DISPOSITION_TYPE"].ToString(), reader["DISPOSITION_CODE"].ToString(), reader["RETURN_CONDITION"].ToString(), reader["QC_REQUIRED"].ToString(), (reader["QC_INSPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["QC_INSPECTED_QTY"].ToString()) : 0M, (reader["QC_REJECTED_QTY"].ToString() != "") ? decimal.Parse(reader["QC_REJECTED_QTY"].ToString()) : 0M, reader["QC_REJ_REASON"].ToString(), reader["QC_STATUS"].ToString(), reader["QC_USER"].ToString(), reader["QC_AUTO_ADJUST"].ToString(), reader["RMA"].ToString(), reader["VENDOR_ID"].ToString(), reader["VENDOR_NAME"].ToString(), reader["MATCH_LOTTABLE"].ToString(), reader["CROSS_DOCK_ID"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime(), reader["SKU_DESCR"].ToString(), reader["STATUS_D"].ToString(), reader["LPN_ID"].ToString(), (reader["DECLARATION_QTY"].ToString() != "") ? decimal.Parse(reader["DECLARATION_QTY"].ToString()) : 0M, reader["SKU_STYLE"].ToString());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return receiptDetailInfo;
        }

        public List<ReceiptDetailInfo> GetReceiptDetailListByPoID(DataBase dataBase, DbTransaction tran, string poID, string poLineID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = string.Empty;
            sql = this.SQL_SELECT_ALL_RECEIPT_DETAIL_LPN.Replace("@_@", whLoginID) + " WHERE RD.PO_ID = @PO_ID AND RD.PO_LINE_ID = @PO_LINE_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@PO_ID", poID), new SqlParameter("@PO_LINE_ID", poLineID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            List<ReceiptDetailInfo> receiptDetailList = new List<ReceiptDetailInfo>();
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            while (reader.Read())
            {
                DateTime CS00002;
                ReceiptDetailInfo receiptDetailInfo = new ReceiptDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["RECEIPT_ID"].ToString(), (reader["RECEIPT_LINE_ID"].ToString() != "") ? int.Parse(reader["RECEIPT_LINE_ID"].ToString()) : 0, reader["EXTERN_RECEIPT_ID"].ToString(), reader["EXTERN_LINE_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["PO_ID"].ToString(), int.Parse((reader["PO_LINE_ID"].ToString() == "") ? "0" : reader["PO_LINE_ID"].ToString()), reader["TARIFF_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["LPN_ID"].ToString(), reader["CASE_ID"].ToString(), reader["STATUS"].ToString(), (reader["RECEIVED_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIVED_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["EXPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["EXPECTED_QTY"].ToString()) : 0M, (reader["ADJUSTED_QTY"].ToString() != "") ? decimal.Parse(reader["ADJUSTED_QTY"].ToString()) : 0M, (reader["RECEIVED_QTY"].ToString() != "") ? decimal.Parse(reader["RECEIVED_QTY"].ToString()) : 0M, (reader["REJECTED_QTY"].ToString() != "") ? decimal.Parse(reader["REJECTED_QTY"].ToString()) : 0M, reader["UOM_ID"].ToString(), reader["PACK_ID"].ToString(), reader["TO_LOC"].ToString(), reader["TO_LOT"].ToString(), reader["TO_LPN_ID"].ToString(), reader["CONDITION_CODE"].ToString(), (reader["CASE_CNT"].ToString() != "") ? decimal.Parse(reader["CASE_CNT"].ToString()) : 0M, (reader["INNER_PACK"].ToString() != "") ? decimal.Parse(reader["INNER_PACK"].ToString()) : 0M, (reader["PALLET"].ToString() != "") ? decimal.Parse(reader["PALLET"].ToString()) : 0M, (reader["CUBE"].ToString() != "") ? float.Parse(reader["CUBE"].ToString()) : 0f, (reader["GROSS_WGT"].ToString() != "") ? float.Parse(reader["GROSS_WGT"].ToString()) : 0f, (reader["NET_WGT"].ToString() != "") ? float.Parse(reader["NET_WGT"].ToString()) : 0f, (reader["LENGTH"].ToString() != "") ? float.Parse(reader["LENGTH"].ToString()) : 0f, (reader["WIDTH"].ToString() != "") ? float.Parse(reader["WIDTH"].ToString()) : 0f, (reader["HEIGHT"].ToString() != "") ? float.Parse(reader["HEIGHT"].ToString()) : 0f, (reader["OTHER_UNIT1"].ToString() != "") ? float.Parse(reader["OTHER_UNIT1"].ToString()) : 0f, (reader["OTHER_UNIT2"].ToString() != "") ? float.Parse(reader["OTHER_UNIT2"].ToString()) : 0f, (reader["UNIT_PRICE"].ToString() != "") ? float.Parse(reader["UNIT_PRICE"].ToString()) : 0f, (reader["EXTENDED_PRICE"].ToString() != "") ? float.Parse(reader["EXTENDED_PRICE"].ToString()) : 0f, (reader["EFFECTIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["EFFECTIVE_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REASON_CODE"].ToString(), reader["PALLET_ID"].ToString(), reader["TYPE"].ToString(), reader["RETURN_TYPE"].ToString(), reader["RETURN_REASON"].ToString(), reader["DISPOSITION_TYPE"].ToString(), reader["DISPOSITION_CODE"].ToString(), reader["RETURN_CONDITION"].ToString(), reader["QC_REQUIRED"].ToString(), (reader["QC_INSPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["QC_INSPECTED_QTY"].ToString()) : 0M, (reader["QC_REJECTED_QTY"].ToString() != "") ? decimal.Parse(reader["QC_REJECTED_QTY"].ToString()) : 0M, reader["QC_REJ_REASON"].ToString(), reader["QC_STATUS"].ToString(), reader["QC_USER"].ToString(), reader["QC_AUTO_ADJUST"].ToString(), reader["RMA"].ToString(), reader["VENDOR_ID"].ToString(), reader["VENDOR_NAME"].ToString(), reader["MATCH_LOTTABLE"].ToString(), reader["CROSS_DOCK_ID"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["SKU_DESCR"].ToString(), reader["STATUS_D"].ToString(), reader["LPN_ID"].ToString(), (reader["DECLARATION_QTY"].ToString() != "") ? decimal.Parse(reader["DECLARATION_QTY"].ToString()) : 0M, reader["SKU_STYLE"].ToString());
                receiptDetailList.Add(receiptDetailInfo);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return receiptDetailList;
        }

        public List<ReceiptDetailInfo> GetReceiptDetailListByWhere(string receiptID, string where, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = string.Empty;
            sql = this.SQL_SELECT_ALL_RECEIPT_DETAIL_LPN.Replace("@_@", whLoginID) + " WHERE RD.RECEIPT_ID = @RECEIPT_ID AND " + where + " ORDER BY RECEIPT_LINE_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", receiptID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            List<ReceiptDetailInfo> receiptDetailInfos = new List<ReceiptDetailInfo>();
            ReceiptDetailInfo receiptDetailInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    DateTime CS00002;
                    receiptDetailInfo = new ReceiptDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["RECEIPT_ID"].ToString(), (reader["RECEIPT_LINE_ID"].ToString() != "") ? int.Parse(reader["RECEIPT_LINE_ID"].ToString()) : 0, reader["EXTERN_RECEIPT_ID"].ToString(), reader["EXTERN_LINE_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["PO_ID"].ToString(), int.Parse((reader["PO_LINE_ID"].ToString() == "") ? "0" : reader["PO_LINE_ID"].ToString()), reader["TARIFF_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["LPN_ID"].ToString(), reader["CASE_ID"].ToString(), reader["STATUS"].ToString(), (reader["RECEIVED_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIVED_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["EXPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["EXPECTED_QTY"].ToString()) : 0M, (reader["ADJUSTED_QTY"].ToString() != "") ? decimal.Parse(reader["ADJUSTED_QTY"].ToString()) : 0M, (reader["RECEIVED_QTY"].ToString() != "") ? decimal.Parse(reader["RECEIVED_QTY"].ToString()) : 0M, (reader["REJECTED_QTY"].ToString() != "") ? decimal.Parse(reader["REJECTED_QTY"].ToString()) : 0M, reader["UOM_ID"].ToString(), reader["PACK_ID"].ToString(), reader["TO_LOC"].ToString(), reader["TO_LOT"].ToString(), reader["TO_LPN_ID"].ToString(), reader["CONDITION_CODE"].ToString(), (reader["CASE_CNT"].ToString() != "") ? decimal.Parse(reader["CASE_CNT"].ToString()) : 0M, (reader["INNER_PACK"].ToString() != "") ? decimal.Parse(reader["INNER_PACK"].ToString()) : 0M, (reader["PALLET"].ToString() != "") ? decimal.Parse(reader["PALLET"].ToString()) : 0M, (reader["CUBE"].ToString() != "") ? float.Parse(reader["CUBE"].ToString()) : 0f, (reader["GROSS_WGT"].ToString() != "") ? float.Parse(reader["GROSS_WGT"].ToString()) : 0f, (reader["NET_WGT"].ToString() != "") ? float.Parse(reader["NET_WGT"].ToString()) : 0f, (reader["LENGTH"].ToString() != "") ? float.Parse(reader["LENGTH"].ToString()) : 0f, (reader["WIDTH"].ToString() != "") ? float.Parse(reader["WIDTH"].ToString()) : 0f, (reader["HEIGHT"].ToString() != "") ? float.Parse(reader["HEIGHT"].ToString()) : 0f, (reader["OTHER_UNIT1"].ToString() != "") ? float.Parse(reader["OTHER_UNIT1"].ToString()) : 0f, (reader["OTHER_UNIT2"].ToString() != "") ? float.Parse(reader["OTHER_UNIT2"].ToString()) : 0f, (reader["UNIT_PRICE"].ToString() != "") ? float.Parse(reader["UNIT_PRICE"].ToString()) : 0f, (reader["EXTENDED_PRICE"].ToString() != "") ? float.Parse(reader["EXTENDED_PRICE"].ToString()) : 0f, (reader["EFFECTIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["EFFECTIVE_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REASON_CODE"].ToString(), reader["PALLET_ID"].ToString(), reader["TYPE"].ToString(), reader["RETURN_TYPE"].ToString(), reader["RETURN_REASON"].ToString(), reader["DISPOSITION_TYPE"].ToString(), reader["DISPOSITION_CODE"].ToString(), reader["RETURN_CONDITION"].ToString(), reader["QC_REQUIRED"].ToString(), (reader["QC_INSPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["QC_INSPECTED_QTY"].ToString()) : 0M, (reader["QC_REJECTED_QTY"].ToString() != "") ? decimal.Parse(reader["QC_REJECTED_QTY"].ToString()) : 0M, reader["QC_REJ_REASON"].ToString(), reader["QC_STATUS"].ToString(), reader["QC_USER"].ToString(), reader["QC_AUTO_ADJUST"].ToString(), reader["RMA"].ToString(), reader["VENDOR_ID"].ToString(), reader["VENDOR_NAME"].ToString(), reader["MATCH_LOTTABLE"].ToString(), reader["CROSS_DOCK_ID"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["SKU_DESCR"].ToString(), reader["STATUS_D"].ToString(), reader["LPN_ID"].ToString(), (reader["DECLARATION_QTY"].ToString() != "") ? decimal.Parse(reader["DECLARATION_QTY"].ToString()) : 0M, reader["SKU_STYLE"].ToString());
                    ReceiptLotInfo receiptLot = new ReceiptLotInfo(reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : (CS00002 = new DateTime()), reader["EXTERNAL_LOT"].ToString(), reader["VENDOR_ID"].ToString());
                    receiptDetailInfo.ReceiptLot = receiptLot;
                    receiptDetailInfos.Add(receiptDetailInfo);
                }
            }
            return receiptDetailInfos;
        }

        public List<ReceiptDetailInfo> GetReceiptDetailListByWhere(DataBase dataBase, DbTransaction tran, string receiptID, string where, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = string.Empty;
            sql = this.SQL_SELECT_ALL_RECEIPT_DETAIL_LPN.Replace("@_@", whLoginID) + " WHERE RD.RECEIPT_ID = @RECEIPT_ID AND " + where + " ORDER BY RECEIPT_LINE_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", receiptID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            List<ReceiptDetailInfo> receiptDetailInfos = new List<ReceiptDetailInfo>();
            ReceiptDetailInfo receiptDetailInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            while (reader.Read())
            {
                DateTime CS00002;
                receiptDetailInfo = new ReceiptDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["RECEIPT_ID"].ToString(), (reader["RECEIPT_LINE_ID"].ToString() != "") ? int.Parse(reader["RECEIPT_LINE_ID"].ToString()) : 0, reader["EXTERN_RECEIPT_ID"].ToString(), reader["EXTERN_LINE_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["PO_ID"].ToString(), int.Parse((reader["PO_LINE_ID"].ToString() == "") ? "0" : reader["PO_LINE_ID"].ToString()), reader["TARIFF_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["LPN_ID"].ToString(), reader["CASE_ID"].ToString(), reader["STATUS"].ToString(), (reader["RECEIVED_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIVED_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["EXPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["EXPECTED_QTY"].ToString()) : 0M, (reader["ADJUSTED_QTY"].ToString() != "") ? decimal.Parse(reader["ADJUSTED_QTY"].ToString()) : 0M, (reader["RECEIVED_QTY"].ToString() != "") ? decimal.Parse(reader["RECEIVED_QTY"].ToString()) : 0M, (reader["REJECTED_QTY"].ToString() != "") ? decimal.Parse(reader["REJECTED_QTY"].ToString()) : 0M, reader["UOM_ID"].ToString(), reader["PACK_ID"].ToString(), reader["TO_LOC"].ToString(), reader["TO_LOT"].ToString(), reader["TO_LPN_ID"].ToString(), reader["CONDITION_CODE"].ToString(), (reader["CASE_CNT"].ToString() != "") ? decimal.Parse(reader["CASE_CNT"].ToString()) : 0M, (reader["INNER_PACK"].ToString() != "") ? decimal.Parse(reader["INNER_PACK"].ToString()) : 0M, (reader["PALLET"].ToString() != "") ? decimal.Parse(reader["PALLET"].ToString()) : 0M, (reader["CUBE"].ToString() != "") ? float.Parse(reader["CUBE"].ToString()) : 0f, (reader["GROSS_WGT"].ToString() != "") ? float.Parse(reader["GROSS_WGT"].ToString()) : 0f, (reader["NET_WGT"].ToString() != "") ? float.Parse(reader["NET_WGT"].ToString()) : 0f, (reader["LENGTH"].ToString() != "") ? float.Parse(reader["LENGTH"].ToString()) : 0f, (reader["WIDTH"].ToString() != "") ? float.Parse(reader["WIDTH"].ToString()) : 0f, (reader["HEIGHT"].ToString() != "") ? float.Parse(reader["HEIGHT"].ToString()) : 0f, (reader["OTHER_UNIT1"].ToString() != "") ? float.Parse(reader["OTHER_UNIT1"].ToString()) : 0f, (reader["OTHER_UNIT2"].ToString() != "") ? float.Parse(reader["OTHER_UNIT2"].ToString()) : 0f, (reader["UNIT_PRICE"].ToString() != "") ? float.Parse(reader["UNIT_PRICE"].ToString()) : 0f, (reader["EXTENDED_PRICE"].ToString() != "") ? float.Parse(reader["EXTENDED_PRICE"].ToString()) : 0f, (reader["EFFECTIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["EFFECTIVE_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REASON_CODE"].ToString(), reader["PALLET_ID"].ToString(), reader["TYPE"].ToString(), reader["RETURN_TYPE"].ToString(), reader["RETURN_REASON"].ToString(), reader["DISPOSITION_TYPE"].ToString(), reader["DISPOSITION_CODE"].ToString(), reader["RETURN_CONDITION"].ToString(), reader["QC_REQUIRED"].ToString(), (reader["QC_INSPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["QC_INSPECTED_QTY"].ToString()) : 0M, (reader["QC_REJECTED_QTY"].ToString() != "") ? decimal.Parse(reader["QC_REJECTED_QTY"].ToString()) : 0M, reader["QC_REJ_REASON"].ToString(), reader["QC_STATUS"].ToString(), reader["QC_USER"].ToString(), reader["QC_AUTO_ADJUST"].ToString(), reader["RMA"].ToString(), reader["VENDOR_ID"].ToString(), reader["VENDOR_NAME"].ToString(), reader["MATCH_LOTTABLE"].ToString(), reader["CROSS_DOCK_ID"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["SKU_DESCR"].ToString(), reader["STATUS_D"].ToString(), reader["LPN_ID"].ToString(), (reader["DECLARATION_QTY"].ToString() != "") ? decimal.Parse(reader["DECLARATION_QTY"].ToString()) : 0M, reader["SKU_STYLE"].ToString());
                ReceiptLotInfo receiptLot = new ReceiptLotInfo(reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : (CS00002 = new DateTime()), reader["EXTERNAL_LOT"].ToString(), reader["VENDOR_ID"].ToString());
                receiptDetailInfo.ReceiptLot = receiptLot;
                receiptDetailInfos.Add(receiptDetailInfo);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return receiptDetailInfos;
        }

        public ReceiptDetailInfo GetReceiptDetailLpnByID(string receiptID, string receiptLineID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_RECEIPT_DETAIL_LPN.Replace("@_@", whLoginID) + " WHERE RD.RECEIPT_ID = @RECEIPT_ID AND RD.RECEIPT_LINE_ID = @RECEIPT_LINE_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", receiptID), new SqlParameter("@RECEIPT_LINE_ID", receiptLineID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            List<ReceiptDetailInfo> receiptDetails = new List<ReceiptDetailInfo>();
            ReceiptDetailInfo receiptDetailInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    DateTime CS00003;
                    receiptDetailInfo = new ReceiptDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["RECEIPT_ID"].ToString(), (reader["RECEIPT_LINE_ID"].ToString() != "") ? int.Parse(reader["RECEIPT_LINE_ID"].ToString()) : 0, reader["EXTERN_RECEIPT_ID"].ToString(), reader["EXTERN_LINE_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["PO_ID"].ToString(), int.Parse((reader["PO_LINE_ID"].ToString() == "") ? "0" : reader["PO_LINE_ID"].ToString()), reader["TARIFF_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["LPN_ID"].ToString(), reader["CASE_ID"].ToString(), reader["STATUS"].ToString(), (reader["RECEIVED_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIVED_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["EXPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["EXPECTED_QTY"].ToString()) : 0M, (reader["ADJUSTED_QTY"].ToString() != "") ? decimal.Parse(reader["ADJUSTED_QTY"].ToString()) : 0M, (reader["RECEIVED_QTY"].ToString() != "") ? decimal.Parse(reader["RECEIVED_QTY"].ToString()) : 0M, (reader["REJECTED_QTY"].ToString() != "") ? decimal.Parse(reader["REJECTED_QTY"].ToString()) : 0M, reader["UOM_ID"].ToString(), reader["PACK_ID"].ToString(), reader["TO_LOC"].ToString(), reader["TO_LOT"].ToString(), reader["TO_LPN_ID"].ToString(), reader["CONDITION_CODE"].ToString(), (reader["CASE_CNT"].ToString() != "") ? decimal.Parse(reader["CASE_CNT"].ToString()) : 0M, (reader["INNER_PACK"].ToString() != "") ? decimal.Parse(reader["INNER_PACK"].ToString()) : 0M, (reader["PALLET"].ToString() != "") ? decimal.Parse(reader["PALLET"].ToString()) : 0M, (reader["CUBE"].ToString() != "") ? float.Parse(reader["CUBE"].ToString()) : 0f, (reader["GROSS_WGT"].ToString() != "") ? float.Parse(reader["GROSS_WGT"].ToString()) : 0f, (reader["NET_WGT"].ToString() != "") ? float.Parse(reader["NET_WGT"].ToString()) : 0f, (reader["LENGTH"].ToString() != "") ? float.Parse(reader["LENGTH"].ToString()) : 0f, (reader["WIDTH"].ToString() != "") ? float.Parse(reader["WIDTH"].ToString()) : 0f, (reader["HEIGHT"].ToString() != "") ? float.Parse(reader["HEIGHT"].ToString()) : 0f, (reader["OTHER_UNIT1"].ToString() != "") ? float.Parse(reader["OTHER_UNIT1"].ToString()) : 0f, (reader["OTHER_UNIT2"].ToString() != "") ? float.Parse(reader["OTHER_UNIT2"].ToString()) : 0f, (reader["UNIT_PRICE"].ToString() != "") ? float.Parse(reader["UNIT_PRICE"].ToString()) : 0f, (reader["EXTENDED_PRICE"].ToString() != "") ? float.Parse(reader["EXTENDED_PRICE"].ToString()) : 0f, (reader["EFFECTIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["EFFECTIVE_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REASON_CODE"].ToString(), reader["PALLET_ID"].ToString(), reader["TYPE"].ToString(), reader["RETURN_TYPE"].ToString(), reader["RETURN_REASON"].ToString(), reader["DISPOSITION_TYPE"].ToString(), reader["DISPOSITION_CODE"].ToString(), reader["RETURN_CONDITION"].ToString(), reader["QC_REQUIRED"].ToString(), (reader["QC_INSPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["QC_INSPECTED_QTY"].ToString()) : 0M, (reader["QC_REJECTED_QTY"].ToString() != "") ? decimal.Parse(reader["QC_REJECTED_QTY"].ToString()) : 0M, reader["QC_REJ_REASON"].ToString(), reader["QC_STATUS"].ToString(), reader["QC_USER"].ToString(), reader["QC_AUTO_ADJUST"].ToString(), reader["RMA"].ToString(), reader["VENDOR_ID"].ToString(), reader["VENDOR_NAME"].ToString(), reader["MATCH_LOTTABLE"].ToString(), reader["CROSS_DOCK_ID"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["SKU_DESCR"].ToString(), reader["STATUS_D"].ToString(), reader["LPN_ID"].ToString(), (reader["DECLARATION_QTY"].ToString() != "") ? decimal.Parse(reader["DECLARATION_QTY"].ToString()) : 0M, reader["SKU_STYLE"].ToString());
                    ReceiptLotInfo receiptLot = new ReceiptLotInfo(reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : new DateTime(), reader["EXTERNAL_LOT"].ToString(), reader["VENDOR_ID"].ToString());
                    receiptDetailInfo.ReceiptLot = receiptLot;
                }
            }
            return receiptDetailInfo;
        }

        public ReceiptDetailInfo GetReceiptDetailLpnByID(DataBase dataBase, DbTransaction tran, string receiptID, string receiptLineID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_RECEIPT_DETAIL_LPN.Replace("@_@", whLoginID) + " WHERE RD.RECEIPT_ID = @RECEIPT_ID AND RD.RECEIPT_LINE_ID = @RECEIPT_LINE_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", receiptID), new SqlParameter("@RECEIPT_LINE_ID", receiptLineID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            List<ReceiptDetailInfo> receiptDetails = new List<ReceiptDetailInfo>();
            ReceiptDetailInfo receiptDetailInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                DateTime CS00003;
                receiptDetailInfo = new ReceiptDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["RECEIPT_ID"].ToString(), (reader["RECEIPT_LINE_ID"].ToString() != "") ? int.Parse(reader["RECEIPT_LINE_ID"].ToString()) : 0, reader["EXTERN_RECEIPT_ID"].ToString(), reader["EXTERN_LINE_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["PO_ID"].ToString(), int.Parse((reader["PO_LINE_ID"].ToString() == "") ? "0" : reader["PO_LINE_ID"].ToString()), reader["TARIFF_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["LPN_ID"].ToString(), reader["CASE_ID"].ToString(), reader["STATUS"].ToString(), (reader["RECEIVED_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIVED_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["EXPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["EXPECTED_QTY"].ToString()) : 0M, (reader["ADJUSTED_QTY"].ToString() != "") ? decimal.Parse(reader["ADJUSTED_QTY"].ToString()) : 0M, (reader["RECEIVED_QTY"].ToString() != "") ? decimal.Parse(reader["RECEIVED_QTY"].ToString()) : 0M, (reader["REJECTED_QTY"].ToString() != "") ? decimal.Parse(reader["REJECTED_QTY"].ToString()) : 0M, reader["UOM_ID"].ToString(), reader["PACK_ID"].ToString(), reader["TO_LOC"].ToString(), reader["TO_LOT"].ToString(), reader["TO_LPN_ID"].ToString(), reader["CONDITION_CODE"].ToString(), (reader["CASE_CNT"].ToString() != "") ? decimal.Parse(reader["CASE_CNT"].ToString()) : 0M, (reader["INNER_PACK"].ToString() != "") ? decimal.Parse(reader["INNER_PACK"].ToString()) : 0M, (reader["PALLET"].ToString() != "") ? decimal.Parse(reader["PALLET"].ToString()) : 0M, (reader["CUBE"].ToString() != "") ? float.Parse(reader["CUBE"].ToString()) : 0f, (reader["GROSS_WGT"].ToString() != "") ? float.Parse(reader["GROSS_WGT"].ToString()) : 0f, (reader["NET_WGT"].ToString() != "") ? float.Parse(reader["NET_WGT"].ToString()) : 0f, (reader["LENGTH"].ToString() != "") ? float.Parse(reader["LENGTH"].ToString()) : 0f, (reader["WIDTH"].ToString() != "") ? float.Parse(reader["WIDTH"].ToString()) : 0f, (reader["HEIGHT"].ToString() != "") ? float.Parse(reader["HEIGHT"].ToString()) : 0f, (reader["OTHER_UNIT1"].ToString() != "") ? float.Parse(reader["OTHER_UNIT1"].ToString()) : 0f, (reader["OTHER_UNIT2"].ToString() != "") ? float.Parse(reader["OTHER_UNIT2"].ToString()) : 0f, (reader["UNIT_PRICE"].ToString() != "") ? float.Parse(reader["UNIT_PRICE"].ToString()) : 0f, (reader["EXTENDED_PRICE"].ToString() != "") ? float.Parse(reader["EXTENDED_PRICE"].ToString()) : 0f, (reader["EFFECTIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["EFFECTIVE_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REASON_CODE"].ToString(), reader["PALLET_ID"].ToString(), reader["TYPE"].ToString(), reader["RETURN_TYPE"].ToString(), reader["RETURN_REASON"].ToString(), reader["DISPOSITION_TYPE"].ToString(), reader["DISPOSITION_CODE"].ToString(), reader["RETURN_CONDITION"].ToString(), reader["QC_REQUIRED"].ToString(), (reader["QC_INSPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["QC_INSPECTED_QTY"].ToString()) : 0M, (reader["QC_REJECTED_QTY"].ToString() != "") ? decimal.Parse(reader["QC_REJECTED_QTY"].ToString()) : 0M, reader["QC_REJ_REASON"].ToString(), reader["QC_STATUS"].ToString(), reader["QC_USER"].ToString(), reader["QC_AUTO_ADJUST"].ToString(), reader["RMA"].ToString(), reader["VENDOR_ID"].ToString(), reader["VENDOR_NAME"].ToString(), reader["MATCH_LOTTABLE"].ToString(), reader["CROSS_DOCK_ID"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["SKU_DESCR"].ToString(), reader["STATUS_D"].ToString(), reader["LPN_ID"].ToString(), (reader["DECLARATION_QTY"].ToString() != "") ? decimal.Parse(reader["DECLARATION_QTY"].ToString()) : 0M, reader["SKU_STYLE"].ToString());
                ReceiptLotInfo receiptLot = new ReceiptLotInfo(reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : new DateTime(), reader["EXTERNAL_LOT"].ToString(), reader["VENDOR_ID"].ToString());
                receiptDetailInfo.ReceiptLot = receiptLot;
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return receiptDetailInfo;
        }

        public List<ReceiptDetailInfo> GetReceiptDetailLpnListByID(string receiptID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_RECEIPT_DETAIL_LPN.Replace("@_@", whLoginID) + " WHERE RD.RECEIPT_ID = @RECEIPT_ID ORDER BY RECEIPT_LINE_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", receiptID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            List<ReceiptDetailInfo> receiptDetails = new List<ReceiptDetailInfo>();
            ReceiptDetailInfo receiptDetailInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    DateTime CS00002;
                    receiptDetailInfo = new ReceiptDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["RECEIPT_ID"].ToString(), (reader["RECEIPT_LINE_ID"].ToString() != "") ? int.Parse(reader["RECEIPT_LINE_ID"].ToString()) : 0, reader["EXTERN_RECEIPT_ID"].ToString(), reader["EXTERN_LINE_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["PO_ID"].ToString(), int.Parse((reader["PO_LINE_ID"].ToString() == "") ? "0" : reader["PO_LINE_ID"].ToString()), reader["TARIFF_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["LPN_ID"].ToString(), reader["CASE_ID"].ToString(), reader["STATUS"].ToString(), (reader["RECEIVED_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIVED_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["EXPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["EXPECTED_QTY"].ToString()) : 0M, (reader["ADJUSTED_QTY"].ToString() != "") ? decimal.Parse(reader["ADJUSTED_QTY"].ToString()) : 0M, (reader["RECEIVED_QTY"].ToString() != "") ? decimal.Parse(reader["RECEIVED_QTY"].ToString()) : 0M, (reader["REJECTED_QTY"].ToString() != "") ? decimal.Parse(reader["REJECTED_QTY"].ToString()) : 0M, reader["UOM_ID"].ToString(), reader["PACK_ID"].ToString(), reader["TO_LOC"].ToString(), reader["TO_LOT"].ToString(), reader["TO_LPN_ID"].ToString(), reader["CONDITION_CODE"].ToString(), (reader["CASE_CNT"].ToString() != "") ? decimal.Parse(reader["CASE_CNT"].ToString()) : 0M, (reader["INNER_PACK"].ToString() != "") ? decimal.Parse(reader["INNER_PACK"].ToString()) : 0M, (reader["PALLET"].ToString() != "") ? decimal.Parse(reader["PALLET"].ToString()) : 0M, (reader["CUBE"].ToString() != "") ? float.Parse(reader["CUBE"].ToString()) : 0f, (reader["GROSS_WGT"].ToString() != "") ? float.Parse(reader["GROSS_WGT"].ToString()) : 0f, (reader["NET_WGT"].ToString() != "") ? float.Parse(reader["NET_WGT"].ToString()) : 0f, (reader["LENGTH"].ToString() != "") ? float.Parse(reader["LENGTH"].ToString()) : 0f, (reader["WIDTH"].ToString() != "") ? float.Parse(reader["WIDTH"].ToString()) : 0f, (reader["HEIGHT"].ToString() != "") ? float.Parse(reader["HEIGHT"].ToString()) : 0f, (reader["OTHER_UNIT1"].ToString() != "") ? float.Parse(reader["OTHER_UNIT1"].ToString()) : 0f, (reader["OTHER_UNIT2"].ToString() != "") ? float.Parse(reader["OTHER_UNIT2"].ToString()) : 0f, (reader["UNIT_PRICE"].ToString() != "") ? float.Parse(reader["UNIT_PRICE"].ToString()) : 0f, (reader["EXTENDED_PRICE"].ToString() != "") ? float.Parse(reader["EXTENDED_PRICE"].ToString()) : 0f, (reader["EFFECTIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["EFFECTIVE_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REASON_CODE"].ToString(), reader["PALLET_ID"].ToString(), reader["TYPE"].ToString(), reader["RETURN_TYPE"].ToString(), reader["RETURN_REASON"].ToString(), reader["DISPOSITION_TYPE"].ToString(), reader["DISPOSITION_CODE"].ToString(), reader["RETURN_CONDITION"].ToString(), reader["QC_REQUIRED"].ToString(), (reader["QC_INSPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["QC_INSPECTED_QTY"].ToString()) : 0M, (reader["QC_REJECTED_QTY"].ToString() != "") ? decimal.Parse(reader["QC_REJECTED_QTY"].ToString()) : 0M, reader["QC_REJ_REASON"].ToString(), reader["QC_STATUS"].ToString(), reader["QC_USER"].ToString(), reader["QC_AUTO_ADJUST"].ToString(), reader["RMA"].ToString(), reader["VENDOR_ID"].ToString(), reader["VENDOR_NAME"].ToString(), reader["MATCH_LOTTABLE"].ToString(), reader["CROSS_DOCK_ID"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["SKU_DESCR"].ToString(), reader["STATUS_D"].ToString(), reader["LPN_ID"].ToString(), reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : (CS00002 = new DateTime()), reader["EXTERNAL_LOT"].ToString(), (reader["DECLARATION_QTY"].ToString() != "") ? decimal.Parse(reader["DECLARATION_QTY"].ToString()) : 0M, reader["SKU_STYLE"].ToString());
                    ReceiptLotInfo receiptLot = new ReceiptLotInfo(reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : (CS00002 = new DateTime()), reader["EXTERNAL_LOT"].ToString(), reader["VENDOR_ID"].ToString());
                    receiptDetailInfo.ReceiptLot = receiptLot;
                    receiptDetails.Add(receiptDetailInfo);
                }
            }
            return receiptDetails;
        }

        public List<ReceiptDetailInfo> GetReceiptDetailLpnListByID(DataBase dataBase, DbTransaction tran, string receiptID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_RECEIPT_DETAIL_LPN.Replace("@_@", whLoginID) + " WHERE RD.RECEIPT_ID = @RECEIPT_ID ORDER BY RECEIPT_LINE_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", receiptID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            List<ReceiptDetailInfo> receiptDetails = new List<ReceiptDetailInfo>();
            ReceiptDetailInfo receiptDetailInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            while (reader.Read())
            {
                DateTime CS00002;
                receiptDetailInfo = new ReceiptDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["RECEIPT_ID"].ToString(), (reader["RECEIPT_LINE_ID"].ToString() != "") ? int.Parse(reader["RECEIPT_LINE_ID"].ToString()) : 0, reader["EXTERN_RECEIPT_ID"].ToString(), reader["EXTERN_LINE_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["PO_ID"].ToString(), int.Parse((reader["PO_LINE_ID"].ToString() == "") ? "0" : reader["PO_LINE_ID"].ToString()), reader["TARIFF_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["LPN_ID"].ToString(), reader["CASE_ID"].ToString(), reader["STATUS"].ToString(), (reader["RECEIVED_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIVED_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["EXPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["EXPECTED_QTY"].ToString()) : 0M, (reader["ADJUSTED_QTY"].ToString() != "") ? decimal.Parse(reader["ADJUSTED_QTY"].ToString()) : 0M, (reader["RECEIVED_QTY"].ToString() != "") ? decimal.Parse(reader["RECEIVED_QTY"].ToString()) : 0M, (reader["REJECTED_QTY"].ToString() != "") ? decimal.Parse(reader["REJECTED_QTY"].ToString()) : 0M, reader["UOM_ID"].ToString(), reader["PACK_ID"].ToString(), reader["TO_LOC"].ToString(), reader["TO_LOT"].ToString(), reader["TO_LPN_ID"].ToString(), reader["CONDITION_CODE"].ToString(), (reader["CASE_CNT"].ToString() != "") ? decimal.Parse(reader["CASE_CNT"].ToString()) : 0M, (reader["INNER_PACK"].ToString() != "") ? decimal.Parse(reader["INNER_PACK"].ToString()) : 0M, (reader["PALLET"].ToString() != "") ? decimal.Parse(reader["PALLET"].ToString()) : 0M, (reader["CUBE"].ToString() != "") ? float.Parse(reader["CUBE"].ToString()) : 0f, (reader["GROSS_WGT"].ToString() != "") ? float.Parse(reader["GROSS_WGT"].ToString()) : 0f, (reader["NET_WGT"].ToString() != "") ? float.Parse(reader["NET_WGT"].ToString()) : 0f, (reader["LENGTH"].ToString() != "") ? float.Parse(reader["LENGTH"].ToString()) : 0f, (reader["WIDTH"].ToString() != "") ? float.Parse(reader["WIDTH"].ToString()) : 0f, (reader["HEIGHT"].ToString() != "") ? float.Parse(reader["HEIGHT"].ToString()) : 0f, (reader["OTHER_UNIT1"].ToString() != "") ? float.Parse(reader["OTHER_UNIT1"].ToString()) : 0f, (reader["OTHER_UNIT2"].ToString() != "") ? float.Parse(reader["OTHER_UNIT2"].ToString()) : 0f, (reader["UNIT_PRICE"].ToString() != "") ? float.Parse(reader["UNIT_PRICE"].ToString()) : 0f, (reader["EXTENDED_PRICE"].ToString() != "") ? float.Parse(reader["EXTENDED_PRICE"].ToString()) : 0f, (reader["EFFECTIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["EFFECTIVE_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REASON_CODE"].ToString(), reader["PALLET_ID"].ToString(), reader["TYPE"].ToString(), reader["RETURN_TYPE"].ToString(), reader["RETURN_REASON"].ToString(), reader["DISPOSITION_TYPE"].ToString(), reader["DISPOSITION_CODE"].ToString(), reader["RETURN_CONDITION"].ToString(), reader["QC_REQUIRED"].ToString(), (reader["QC_INSPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["QC_INSPECTED_QTY"].ToString()) : 0M, (reader["QC_REJECTED_QTY"].ToString() != "") ? decimal.Parse(reader["QC_REJECTED_QTY"].ToString()) : 0M, reader["QC_REJ_REASON"].ToString(), reader["QC_STATUS"].ToString(), reader["QC_USER"].ToString(), reader["QC_AUTO_ADJUST"].ToString(), reader["RMA"].ToString(), reader["VENDOR_ID"].ToString(), reader["VENDOR_NAME"].ToString(), reader["MATCH_LOTTABLE"].ToString(), reader["CROSS_DOCK_ID"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["SKU_DESCR"].ToString(), reader["STATUS_D"].ToString(), reader["LPN_ID"].ToString(), reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : (CS00002 = new DateTime()), reader["EXTERNAL_LOT"].ToString(), (reader["DECLARATION_QTY"].ToString() != "") ? decimal.Parse(reader["DECLARATION_QTY"].ToString()) : 0M, reader["SKU_STYLE"].ToString());
                ReceiptLotInfo receiptLot = new ReceiptLotInfo(reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : (CS00002 = new DateTime()), reader["EXTERNAL_LOT"].ToString(), reader["VENDOR_ID"].ToString());
                receiptDetailInfo.ReceiptLot = receiptLot;
                receiptDetails.Add(receiptDetailInfo);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return receiptDetails;
        }

        public DataSet GetReceiptDetailTotalExpectedAndReceivedQty(string receiptID, string whLoginID)
        {
            string sql = this.SQL_GET_TOTAL_EXPECTED_QTY_AND_RECEIVED_QTY.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", receiptID) };
            return DBHelper.ExecuteDataSet(CommandType.Text, sql, paras);
        }

        public DataSet GetReceiptDetailTotalExpectedAndReceivedQty(DataBase dataBase, DbTransaction tran, string receiptID, string whLoginID)
        {
            string sql = this.SQL_GET_TOTAL_EXPECTED_QTY_AND_RECEIVED_QTY.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", receiptID) };
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql, paras);
        }

        public DataSet GetReceiptHistoryByPagerQueryList(List<string> sqlWhere, ReceiptHistoryQueryEntity receiptHistoryQueryEntity, string whLoginID, PubParasInfo pubParasInfo)
        {
            string where = "WHERE IT.TRANS_TYPE = 'IN' AND SOURCE_TRANS_TYPE = 'ReceiptDetailAdd' AND IT.STATUS = 'OK' AND IT.RECEIPT_ID = @RECEIPT_ID";
            string temp = PagingHelper.GetPagingSQL(this.SQL_SELECT_ALL_RECEIPT_HISTORY_LOT_BY_PAGER_QUERY.Replace("@CONDITION@", where).Replace("@_@", whLoginID), receiptHistoryQueryEntity.CurrentPage, receiptHistoryQueryEntity.PageSize, receiptHistoryQueryEntity.SortField, receiptHistoryQueryEntity.SortDirection).Replace("@RECEIPT_ID", "''" + receiptHistoryQueryEntity.ReceiptID + "''");
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public List<ReceiptHistoryInfo> GetReceiptHistoryInfoListByID(string receiptID, string whLoginID)
        {
            string where = "WHERE IT.TRANS_TYPE = 'IN' AND SOURCE_TRANS_TYPE = 'ReceiptDetailAdd' AND IT.STATUS = 'OK' AND IT.RECEIPT_ID = @RECEIPT_ID";
            string sql = this.SQL_SELECT_ALL_RECEIPT_HISTORY_LOT.Replace("@CONDITION@", where).Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", receiptID) };
            List<ReceiptHistoryInfo> receiptHistoryInfos = new List<ReceiptHistoryInfo>();
            ReceiptHistoryInfo receiptHistoryInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    DateTime CS00002;
                    receiptHistoryInfo = new ReceiptHistoryInfo(reader["TRANS_ID"].ToString(), reader["RECEIPT_ID"].ToString(), reader["RECEIPT_LINE_ID"].ToString(), reader["SKU_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["VENDOR_ID"].ToString(), reader["PACK_ID"].ToString(), reader["UOM_ID"].ToString(), reader["TO_LPN_ID"].ToString(), reader["LOT"].ToString(), reader["TO_LOC"].ToString(), decimal.Parse((reader["QTY"].ToString() == "") ? "0" : reader["QTY"].ToString()), reader["HOLD_CODE"].ToString(), decimal.Parse((reader["COMPLETED_QTY"].ToString() == "") ? "0" : reader["COMPLETED_QTY"].ToString()), decimal.Parse((reader["REMAIN_QTY"].ToString() == "") ? "0" : reader["REMAIN_QTY"].ToString()), decimal.Parse((reader["ALLOCATED_QTY"].ToString() == "") ? "0" : reader["ALLOCATED_QTY"].ToString()), reader["DESCR"].ToString(), reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : (CS00002 = new DateTime()), reader["EXTERNAL_LOT"].ToString());
                    if (receiptHistoryInfo.ReceivedQty == (receiptHistoryInfo.CompletedQty + receiptHistoryInfo.AllocatedQty))
                    {
                        receiptHistoryInfo.Status = "Completed";
                        if (receiptHistoryInfo.ReceivedQty == receiptHistoryInfo.AllocatedQty)
                        {
                            receiptHistoryInfo.IsPutAwayed = "N";
                        }
                        else
                        {
                            receiptHistoryInfo.IsPutAwayed = "Y";
                        }
                    }
                    else
                    {
                        receiptHistoryInfo.Status = "Pending";
                        receiptHistoryInfo.IsPutAwayed = "N";
                    }
                    receiptHistoryInfos.Add(receiptHistoryInfo);
                }
            }
            return receiptHistoryInfos;
        }

        public List<ReceiptHistoryInfo> GetReceiptHistoryInfoListByID(DataBase dataBase, DbTransaction tran, string receiptID, string whLoginID)
        {
            string where = "WHERE IT.TRANS_TYPE = 'IN' AND SOURCE_TRANS_TYPE = 'ReceiptDetailAdd' AND IT.STATUS = 'OK' AND IT.RECEIPT_ID = @RECEIPT_ID";
            string sql = this.SQL_SELECT_ALL_RECEIPT_HISTORY_LOT.Replace("@CONDITION@", where).Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", receiptID) };
            List<ReceiptHistoryInfo> receiptHistoryInfos = new List<ReceiptHistoryInfo>();
            ReceiptHistoryInfo receiptHistoryInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            while (reader.Read())
            {
                DateTime CS00002;
                receiptHistoryInfo = new ReceiptHistoryInfo(reader["TRANS_ID"].ToString(), reader["RECEIPT_ID"].ToString(), reader["RECEIPT_LINE_ID"].ToString(), reader["SKU_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["VENDOR_ID"].ToString(), reader["PACK_ID"].ToString(), reader["UOM_ID"].ToString(), reader["TO_LPN_ID"].ToString(), reader["LOT"].ToString(), reader["TO_LOC"].ToString(), decimal.Parse((reader["QTY"].ToString() == "") ? "0" : reader["QTY"].ToString()), reader["HOLD_CODE"].ToString(), decimal.Parse((reader["COMPLETED_QTY"].ToString() == "") ? "0" : reader["COMPLETED_QTY"].ToString()), decimal.Parse((reader["REMAIN_QTY"].ToString() == "") ? "0" : reader["REMAIN_QTY"].ToString()), decimal.Parse((reader["ALLOCATED_QTY"].ToString() == "") ? "0" : reader["ALLOCATED_QTY"].ToString()), reader["DESCR"].ToString(), reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : (CS00002 = new DateTime()), reader["EXTERNAL_LOT"].ToString());
                if (receiptHistoryInfo.ReceivedQty == (receiptHistoryInfo.CompletedQty + receiptHistoryInfo.AllocatedQty))
                {
                    receiptHistoryInfo.Status = "Completed";
                    if (receiptHistoryInfo.ReceivedQty == receiptHistoryInfo.AllocatedQty)
                    {
                        receiptHistoryInfo.IsPutAwayed = "N";
                    }
                    else
                    {
                        receiptHistoryInfo.IsPutAwayed = "Y";
                    }
                }
                else
                {
                    receiptHistoryInfo.Status = "Pending";
                    receiptHistoryInfo.IsPutAwayed = "N";
                }
                receiptHistoryInfos.Add(receiptHistoryInfo);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return receiptHistoryInfos;
        }

        public List<ReceiptHistoryInfo> GetReceiptHistoryInfoListByWhere(string receiptID, string where, string whLoginID)
        {
            string where2 = "WHERE IT.TRANS_TYPE = 'IN' AND SOURCE_TRANS_TYPE = 'ReceiptDetailAdd' AND IT.STATUS = 'OK' AND IT.RECEIPT_ID = @RECEIPT_ID AND " + where;
            string sql = this.SQL_SELECT_ALL_RECEIPT_HISTORY.Replace("@CONDITION@", where2).Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", receiptID) };
            List<ReceiptHistoryInfo> receiptHistoryInfos = new List<ReceiptHistoryInfo>();
            ReceiptHistoryInfo receiptHistoryInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    receiptHistoryInfo = new ReceiptHistoryInfo(reader["TRANS_ID"].ToString(), reader["RECEIPT_ID"].ToString(), reader["RECEIPT_LINE_ID"].ToString(), reader["SKU_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["VENDOR_ID"].ToString(), reader["PACK_ID"].ToString(), reader["UOM_ID"].ToString(), reader["TO_LPN_ID"].ToString(), reader["LOT"].ToString(), reader["TO_LOC"].ToString(), decimal.Parse((reader["QTY"].ToString() == "") ? "0" : reader["QTY"].ToString()), reader["HOLD_CODE"].ToString(), decimal.Parse((reader["COMPLETED_QTY"].ToString() == "") ? "0" : reader["COMPLETED_QTY"].ToString()), decimal.Parse((reader["REMAIN_QTY"].ToString() == "") ? "0" : reader["REMAIN_QTY"].ToString()), decimal.Parse((reader["ALLOCATED_QTY"].ToString() == "") ? "0" : reader["ALLOCATED_QTY"].ToString()));
                    if (receiptHistoryInfo.ReceivedQty == (receiptHistoryInfo.CompletedQty + receiptHistoryInfo.AllocatedQty))
                    {
                        receiptHistoryInfo.Status = "Completed";
                        if (receiptHistoryInfo.ReceivedQty == receiptHistoryInfo.AllocatedQty)
                        {
                            receiptHistoryInfo.IsPutAwayed = "N";
                        }
                        else
                        {
                            receiptHistoryInfo.IsPutAwayed = "Y";
                        }
                    }
                    else
                    {
                        receiptHistoryInfo.Status = "Pending";
                        receiptHistoryInfo.IsPutAwayed = "N";
                    }
                    receiptHistoryInfos.Add(receiptHistoryInfo);
                }
            }
            return receiptHistoryInfos;
        }

        public List<ReceiptHistoryInfo> GetReceiptHistoryInfoListByWhere(DataBase dataBase, DbTransaction tran, string receiptID, string where, string whLoginID)
        {
            string where2 = "WHERE IT.TRANS_TYPE = 'IN' AND SOURCE_TRANS_TYPE = 'ReceiptDetailAdd' AND IT.STATUS = 'OK' AND IT.RECEIPT_ID = @RECEIPT_ID AND " + where;
            string sql = this.SQL_SELECT_ALL_RECEIPT_HISTORY.Replace("@CONDITION@", where2).Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", receiptID) };
            List<ReceiptHistoryInfo> receiptHistoryInfos = new List<ReceiptHistoryInfo>();
            ReceiptHistoryInfo receiptHistoryInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            while (reader.Read())
            {
                receiptHistoryInfo = new ReceiptHistoryInfo(reader["TRANS_ID"].ToString(), reader["RECEIPT_ID"].ToString(), reader["RECEIPT_LINE_ID"].ToString(), reader["SKU_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["VENDOR_ID"].ToString(), reader["PACK_ID"].ToString(), reader["UOM_ID"].ToString(), reader["TO_LPN_ID"].ToString(), reader["LOT"].ToString(), reader["TO_LOC"].ToString(), decimal.Parse((reader["QTY"].ToString() == "") ? "0" : reader["QTY"].ToString()), reader["HOLD_CODE"].ToString(), decimal.Parse((reader["COMPLETED_QTY"].ToString() == "") ? "0" : reader["COMPLETED_QTY"].ToString()), decimal.Parse((reader["REMAIN_QTY"].ToString() == "") ? "0" : reader["REMAIN_QTY"].ToString()), decimal.Parse((reader["ALLOCATED_QTY"].ToString() == "") ? "0" : reader["ALLOCATED_QTY"].ToString()));
                if (receiptHistoryInfo.ReceivedQty == (receiptHistoryInfo.CompletedQty + receiptHistoryInfo.AllocatedQty))
                {
                    receiptHistoryInfo.Status = "Completed";
                    if (receiptHistoryInfo.ReceivedQty == receiptHistoryInfo.AllocatedQty)
                    {
                        receiptHistoryInfo.IsPutAwayed = "N";
                    }
                    else
                    {
                        receiptHistoryInfo.IsPutAwayed = "Y";
                    }
                }
                else
                {
                    receiptHistoryInfo.Status = "Pending";
                    receiptHistoryInfo.IsPutAwayed = "N";
                }
                receiptHistoryInfos.Add(receiptHistoryInfo);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return receiptHistoryInfos;
        }

        public int InsertReceiptDetailLpn(ReceiptDetailInfo receiptDetailInfo, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                receiptDetailInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_ReceiptDetailLpn_Parameters(receiptDetailInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_RECEIPT_DETAIL_LPN.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int InsertReceiptDetailLpn(DataBase dataBase, DbTransaction tran, ReceiptDetailLpnInfo receiptDetailLpnInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                receiptDetailLpnInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_ReceiptDetailLpn_Parameters(receiptDetailLpnInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_RECEIPT_DETAIL_LPN.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int IsCopyReceiptDetailLpn(DataBase dataBase, DbTransaction tran, string newReceiptID, string oldReceiptID, string userID, string whLoginID)
        {
            int result = 0;
            try
            {
                string strSql = this.SQL_IS_COPY.Replace("@_@", whLoginID).Replace("@RECEIPT_ID", newReceiptID).Replace("@OLD_RECEIPT_ID", oldReceiptID).Replace("@USER_ID", userID);
                result += dataBase.ExecuteNonQuery(tran, CommandType.Text, strSql);
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int ReceiptDetailLpnPriceChanges(DataBase dataBase, DbTransaction tran, string receiptID, string lineID, double price, double extendedPrice, string whLoginID, string updatedBy)
        {
            int result = 0;
            try
            {
                string sql = this.SQL_UPDATE_RECEIPT_DETAIL_PRICE.Replace("@_@", whLoginID);
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@UNIT_PRICE", price), new SqlParameter("@EXTENDED_PRICE", extendedPrice), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@RECEIPT_ID", receiptID), new SqlParameter("@RECEIPT_LINE_ID", lineID) };
                result = dataBase.ExecuteNonQuery(tran, CommandType.Text, sql, paras);
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        private SqlParameter[] Set_ReceiptDetailLpn_Parameters(ReceiptDetailInfo receiptDetailLpnInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { 
                new SqlParameter(this.WH_ID, SqlDbType.VarChar, 30), new SqlParameter(this.RECEIPT_ID, SqlDbType.VarChar, 20), new SqlParameter(this.RECEIPT_LINE_ID, SqlDbType.Int, 4), new SqlParameter(this.EXTERN_RECEIPT_ID, SqlDbType.VarChar, 50), new SqlParameter(this.EXTERN_LINE_ID, SqlDbType.VarChar, 20), new SqlParameter(this.OWNER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.PO_ID, SqlDbType.VarChar, 20), new SqlParameter(this.PO_LINE_ID, SqlDbType.Int, 4), new SqlParameter(this.TARIFF_ID, SqlDbType.VarChar, 20), new SqlParameter(this.SKU_ID, SqlDbType.VarChar, 50), new SqlParameter(this.ALT_SKU_ID, SqlDbType.VarChar, 50), new SqlParameter(this.LPN_ID, SqlDbType.VarChar, 20), new SqlParameter(this.CASE_ID, SqlDbType.VarChar, 20), new SqlParameter(this.STATUS, SqlDbType.VarChar, 20), new SqlParameter(this.RECEIVED_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.EXPECTED_QTY, SqlDbType.Decimal, 13), 
                new SqlParameter(this.ADJUSTED_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.RECEIVED_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.REJECTED_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.UOM_ID, SqlDbType.VarChar, 20), new SqlParameter(this.PACK_ID, SqlDbType.VarChar, 50), new SqlParameter(this.TO_LOC, SqlDbType.VarChar, 20), new SqlParameter(this.TO_LOT, SqlDbType.VarChar, 20), new SqlParameter(this.TO_LPN_ID, SqlDbType.VarChar, 20), new SqlParameter(this.CONDITION_CODE, SqlDbType.VarChar, 20), new SqlParameter(this.LOT_ATTR01, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR02, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR03, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR04, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR05, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR06, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR07, SqlDbType.VarChar, 50), 
                new SqlParameter(this.LOT_ATTR08, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR09, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR10, SqlDbType.VarChar, 50), new SqlParameter(this.PRODUCE_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.EXPIRY_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.RECEIPT_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.EXTERNAL_LOT, SqlDbType.VarChar, 100), new SqlParameter(this.CASE_CNT, SqlDbType.Decimal, 13), new SqlParameter(this.INNER_PACK, SqlDbType.Decimal, 13), new SqlParameter(this.PALLET, SqlDbType.Decimal, 13), new SqlParameter(this.CUBE, SqlDbType.Float, 8), new SqlParameter(this.GROSS_WGT, SqlDbType.Float, 8), new SqlParameter(this.NET_WGT, SqlDbType.Float, 8), new SqlParameter(this.LENGTH, SqlDbType.Float, 8), new SqlParameter(this.WIDTH, SqlDbType.Float, 8), new SqlParameter(this.HEIGHT, SqlDbType.Float, 8), 
                new SqlParameter(this.OTHER_UNIT1, SqlDbType.Float, 8), new SqlParameter(this.OTHER_UNIT2, SqlDbType.Float, 8), new SqlParameter(this.UNIT_PRICE, SqlDbType.Float, 8), new SqlParameter(this.EXTENDED_PRICE, SqlDbType.Float, 8), new SqlParameter(this.EFFECTIVE_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.UDF1, SqlDbType.VarChar, 30), new SqlParameter(this.UDF2, SqlDbType.VarChar, 30), new SqlParameter(this.UDF3, SqlDbType.VarChar, 30), new SqlParameter(this.UDF4, SqlDbType.VarChar, 30), new SqlParameter(this.UDF5, SqlDbType.VarChar, 30), new SqlParameter(this.UDF6, SqlDbType.VarChar, 30), new SqlParameter(this.UDF7, SqlDbType.VarChar, 30), new SqlParameter(this.UDF8, SqlDbType.VarChar, 30), new SqlParameter(this.UDF9, SqlDbType.VarChar, 30), new SqlParameter(this.UDF10, SqlDbType.VarChar, 30), new SqlParameter(this.REASON_CODE, SqlDbType.VarChar, 20), 
                new SqlParameter(this.PALLET_ID, SqlDbType.VarChar, 20), new SqlParameter(this.TYPE, SqlDbType.VarChar, 20), new SqlParameter(this.RETURN_TYPE, SqlDbType.VarChar, 20), new SqlParameter(this.RETURN_REASON, SqlDbType.VarChar, 20), new SqlParameter(this.DISPOSITION_TYPE, SqlDbType.VarChar, 20), new SqlParameter(this.DISPOSITION_CODE, SqlDbType.VarChar, 20), new SqlParameter(this.RETURN_CONDITION, SqlDbType.VarChar, 20), new SqlParameter(this.QC_REQUIRED, SqlDbType.VarChar, 1), new SqlParameter(this.QC_INSPECTED_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.QC_REJECTED_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.QC_REJ_REASON, SqlDbType.VarChar, 20), new SqlParameter(this.QC_STATUS, SqlDbType.VarChar, 1), new SqlParameter(this.QC_USER, SqlDbType.VarChar, 20), new SqlParameter(this.QC_AUTO_ADJUST, SqlDbType.VarChar, 1), new SqlParameter(this.RMA, SqlDbType.VarChar, 20), new SqlParameter(this.VENDOR_ID, SqlDbType.VarChar, 20), 
                new SqlParameter(this.VENDOR_NAME, SqlDbType.VarChar, 30), new SqlParameter(this.MATCH_LOTTABLE, SqlDbType.VarChar, 1), new SqlParameter(this.CROSS_DOCK_ID, SqlDbType.VarChar, 20), new SqlParameter(this.REMARK, SqlDbType.VarChar, 0x3e8), new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.DECLARATION_QTY, SqlDbType.Decimal, 13)
             };
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.WhID))
            {
                paramArray[0].Value = receiptDetailLpnInfo.WhID;
            }
            else
            {
                paramArray[0].Value = DBNull.Value;
            }
            paramArray[1].Value = receiptDetailLpnInfo.ReceiptID;
            paramArray[2].Value = receiptDetailLpnInfo.ReceiptLineID;
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.ExternReceiptID))
            {
                paramArray[3].Value = receiptDetailLpnInfo.ExternReceiptID;
            }
            else
            {
                paramArray[3].Value = DBNull.Value;
            }
            paramArray[4].Value = receiptDetailLpnInfo.ExternLineID;
            paramArray[5].Value = receiptDetailLpnInfo.OwnerID;
            paramArray[6].Value = receiptDetailLpnInfo.PoID;
            paramArray[7].Value = receiptDetailLpnInfo.PoLineID;
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.TariffID))
            {
                paramArray[8].Value = receiptDetailLpnInfo.TariffID;
            }
            else
            {
                paramArray[8].Value = DBNull.Value;
            }
            paramArray[9].Value = receiptDetailLpnInfo.SkuID;
            paramArray[10].Value = receiptDetailLpnInfo.AltSkuID;
            paramArray[11].Value = receiptDetailLpnInfo.LpnID;
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.CaseID))
            {
                paramArray[12].Value = receiptDetailLpnInfo.CaseID;
            }
            else
            {
                paramArray[12].Value = DBNull.Value;
            }
            paramArray[13].Value = receiptDetailLpnInfo.Status;
            DateTime CS00003 = new DateTime();
            if (receiptDetailLpnInfo.ReceivedDate != CS00003)
            {
                paramArray[14].Value = receiptDetailLpnInfo.ReceivedDate;
            }
            else
            {
                paramArray[14].Value = DBNull.Value;
            }
            paramArray[15].Value = receiptDetailLpnInfo.ExpectedQty;
            paramArray[0x10].Value = receiptDetailLpnInfo.AdjustedQty;
            paramArray[0x11].Value = receiptDetailLpnInfo.ReceivedQty;
            paramArray[0x12].Value = receiptDetailLpnInfo.RejectedQty;
            paramArray[0x13].Value = receiptDetailLpnInfo.UomID;
            paramArray[20].Value = receiptDetailLpnInfo.PackID;
            paramArray[0x15].Value = receiptDetailLpnInfo.ToLoc;
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.ToLot))
            {
                paramArray[0x16].Value = receiptDetailLpnInfo.ToLot;
            }
            else
            {
                paramArray[0x16].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.ToLpnID))
            {
                paramArray[0x17].Value = receiptDetailLpnInfo.ToLpnID;
            }
            else
            {
                paramArray[0x17].Value = DBNull.Value;
            }
            paramArray[0x18].Value = receiptDetailLpnInfo.ConditionCode;
            paramArray[0x19].Value = receiptDetailLpnInfo.ReceiptLot.LotAttr01;
            paramArray[0x1a].Value = receiptDetailLpnInfo.ReceiptLot.LotAttr02;
            paramArray[0x1b].Value = receiptDetailLpnInfo.ReceiptLot.LotAttr03;
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.ReceiptLot.LotAttr04))
            {
                paramArray[0x1c].Value = receiptDetailLpnInfo.ReceiptLot.LotAttr04;
            }
            else
            {
                paramArray[0x1c].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.ReceiptLot.LotAttr05))
            {
                paramArray[0x1d].Value = receiptDetailLpnInfo.ReceiptLot.LotAttr05;
            }
            else
            {
                paramArray[0x1d].Value = DBNull.Value;
            }
            paramArray[30].Value = receiptDetailLpnInfo.ReceiptLot.LotAttr06;
            paramArray[0x1f].Value = receiptDetailLpnInfo.ReceiptLot.LotAttr07;
            paramArray[0x20].Value = receiptDetailLpnInfo.ReceiptLot.LotAttr08;
            paramArray[0x21].Value = receiptDetailLpnInfo.ReceiptLot.LotAttr09;
            paramArray[0x22].Value = receiptDetailLpnInfo.ReceiptLot.LotAttr10;
            CS00003 = new DateTime();
            if (receiptDetailLpnInfo.ReceiptLot.ProduceDate != CS00003)
            {
                paramArray[0x23].Value = receiptDetailLpnInfo.ReceiptLot.ProduceDate;
            }
            else
            {
                paramArray[0x23].Value = DBNull.Value;
            }
            CS00003 = new DateTime();
            if (receiptDetailLpnInfo.ReceiptLot.ExpiryDate != CS00003)
            {
                paramArray[0x24].Value = receiptDetailLpnInfo.ReceiptLot.ExpiryDate;
            }
            else
            {
                paramArray[0x24].Value = DBNull.Value;
            }
            if (receiptDetailLpnInfo.ReceiptLot.ReceiptDate != new DateTime())
            {
                paramArray[0x25].Value = receiptDetailLpnInfo.ReceiptLot.ReceiptDate;
            }
            else
            {
                paramArray[0x25].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.ReceiptLot.ExternalLot))
            {
                paramArray[0x26].Value = receiptDetailLpnInfo.ReceiptLot.ExternalLot;
            }
            else
            {
                paramArray[0x26].Value = DBNull.Value;
            }
            paramArray[0x27].Value = receiptDetailLpnInfo.CaseCnt;
            paramArray[40].Value = receiptDetailLpnInfo.InnerPack;
            paramArray[0x29].Value = receiptDetailLpnInfo.Pallet;
            paramArray[0x2a].Value = receiptDetailLpnInfo.Cube;
            paramArray[0x2b].Value = receiptDetailLpnInfo.GrossWgt;
            paramArray[0x2c].Value = receiptDetailLpnInfo.NetWgt;
            paramArray[0x2d].Value = receiptDetailLpnInfo.Length;
            paramArray[0x2e].Value = receiptDetailLpnInfo.Width;
            paramArray[0x2f].Value = receiptDetailLpnInfo.Height;
            paramArray[0x30].Value = receiptDetailLpnInfo.OtherUnit1;
            paramArray[0x31].Value = receiptDetailLpnInfo.OtherUnit2;
            paramArray[50].Value = receiptDetailLpnInfo.UnitPrice;
            paramArray[0x33].Value = receiptDetailLpnInfo.ExtendedPrice;
            paramArray[0x34].Value = receiptDetailLpnInfo.EffectiveDate;
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.Udf1))
            {
                paramArray[0x35].Value = receiptDetailLpnInfo.Udf1;
            }
            else
            {
                paramArray[0x35].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.Udf2))
            {
                paramArray[0x36].Value = receiptDetailLpnInfo.Udf2;
            }
            else
            {
                paramArray[0x36].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.Udf3))
            {
                paramArray[0x37].Value = receiptDetailLpnInfo.Udf3;
            }
            else
            {
                paramArray[0x37].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.Udf4))
            {
                paramArray[0x38].Value = receiptDetailLpnInfo.Udf4;
            }
            else
            {
                paramArray[0x38].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.Udf5))
            {
                paramArray[0x39].Value = receiptDetailLpnInfo.Udf5;
            }
            else
            {
                paramArray[0x39].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.Udf6))
            {
                paramArray[0x3a].Value = receiptDetailLpnInfo.Udf6;
            }
            else
            {
                paramArray[0x3a].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.Udf7))
            {
                paramArray[0x3b].Value = receiptDetailLpnInfo.Udf7;
            }
            else
            {
                paramArray[0x3b].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.Udf8))
            {
                paramArray[60].Value = receiptDetailLpnInfo.Udf8;
            }
            else
            {
                paramArray[60].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.Udf9))
            {
                paramArray[0x3d].Value = receiptDetailLpnInfo.Udf9;
            }
            else
            {
                paramArray[0x3d].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.Udf10))
            {
                paramArray[0x3e].Value = receiptDetailLpnInfo.Udf10;
            }
            else
            {
                paramArray[0x3e].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.ReasonCode))
            {
                paramArray[0x3f].Value = receiptDetailLpnInfo.ReasonCode;
            }
            else
            {
                paramArray[0x3f].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.PalletID))
            {
                paramArray[0x40].Value = receiptDetailLpnInfo.PalletID;
            }
            else
            {
                paramArray[0x40].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.Type))
            {
                paramArray[0x41].Value = receiptDetailLpnInfo.Type;
            }
            else
            {
                paramArray[0x41].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.ReturnType))
            {
                paramArray[0x42].Value = receiptDetailLpnInfo.ReturnType;
            }
            else
            {
                paramArray[0x42].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.ReturnReason))
            {
                paramArray[0x43].Value = receiptDetailLpnInfo.ReturnReason;
            }
            else
            {
                paramArray[0x43].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.DispositionType))
            {
                paramArray[0x44].Value = receiptDetailLpnInfo.DispositionType;
            }
            else
            {
                paramArray[0x44].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.DispositionCode))
            {
                paramArray[0x45].Value = receiptDetailLpnInfo.DispositionCode;
            }
            else
            {
                paramArray[0x45].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.ReturnCondition))
            {
                paramArray[70].Value = receiptDetailLpnInfo.ReturnCondition;
            }
            else
            {
                paramArray[70].Value = DBNull.Value;
            }
            paramArray[0x47].Value = receiptDetailLpnInfo.QcRequired;
            paramArray[0x48].Value = receiptDetailLpnInfo.QcInspectedQty;
            paramArray[0x49].Value = receiptDetailLpnInfo.QcRejectedQty;
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.QcRejReason))
            {
                paramArray[0x4a].Value = receiptDetailLpnInfo.QcRejReason;
            }
            else
            {
                paramArray[0x4a].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.QcStatus))
            {
                paramArray[0x4b].Value = receiptDetailLpnInfo.QcStatus;
            }
            else
            {
                paramArray[0x4b].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.QcUser))
            {
                paramArray[0x4c].Value = receiptDetailLpnInfo.QcUser;
            }
            else
            {
                paramArray[0x4c].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.QcAutoAdjust))
            {
                paramArray[0x4d].Value = receiptDetailLpnInfo.QcAutoAdjust;
            }
            else
            {
                paramArray[0x4d].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.Rma))
            {
                paramArray[0x4e].Value = receiptDetailLpnInfo.Rma;
            }
            else
            {
                paramArray[0x4e].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.VendorID))
            {
                paramArray[0x4f].Value = receiptDetailLpnInfo.VendorID;
            }
            else
            {
                paramArray[0x4f].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.VendorName))
            {
                paramArray[80].Value = receiptDetailLpnInfo.VendorName;
            }
            else
            {
                paramArray[80].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.MatchLottable))
            {
                paramArray[0x51].Value = receiptDetailLpnInfo.MatchLottable;
            }
            else
            {
                paramArray[0x51].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.CrossDockID))
            {
                paramArray[0x52].Value = receiptDetailLpnInfo.CrossDockID;
            }
            else
            {
                paramArray[0x52].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.Remark))
            {
                paramArray[0x53].Value = receiptDetailLpnInfo.Remark;
            }
            else
            {
                paramArray[0x53].Value = DBNull.Value;
            }
            paramArray[0x54].Value = receiptDetailLpnInfo.CreatedBy;
            paramArray[0x55].Value = receiptDetailLpnInfo.UpdatedBy;
            paramArray[0x56].Value = receiptDetailLpnInfo.DeclarationQty;
            return paramArray;
        }

        private SqlParameter[] Set_ReceiptDetailLpn_Parameters(ReceiptDetailLpnInfo receiptDetailLpnInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { 
                new SqlParameter(this.WH_ID, SqlDbType.VarChar, 30), new SqlParameter(this.RECEIPT_ID, SqlDbType.VarChar, 20), new SqlParameter(this.RECEIPT_LINE_ID, SqlDbType.Int, 4), new SqlParameter(this.EXTERN_RECEIPT_ID, SqlDbType.VarChar, 50), new SqlParameter(this.EXTERN_LINE_ID, SqlDbType.VarChar, 20), new SqlParameter(this.OWNER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.PO_ID, SqlDbType.VarChar, 20), new SqlParameter(this.PO_LINE_ID, SqlDbType.Int, 4), new SqlParameter(this.TARIFF_ID, SqlDbType.VarChar, 20), new SqlParameter(this.SKU_ID, SqlDbType.VarChar, 50), new SqlParameter(this.ALT_SKU_ID, SqlDbType.VarChar, 50), new SqlParameter(this.LPN_ID, SqlDbType.VarChar, 20), new SqlParameter(this.CASE_ID, SqlDbType.VarChar, 20), new SqlParameter(this.STATUS, SqlDbType.VarChar, 20), new SqlParameter(this.RECEIVED_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.EXPECTED_QTY, SqlDbType.Decimal, 13), 
                new SqlParameter(this.ADJUSTED_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.RECEIVED_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.REJECTED_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.UOM_ID, SqlDbType.VarChar, 20), new SqlParameter(this.PACK_ID, SqlDbType.VarChar, 50), new SqlParameter(this.TO_LOC, SqlDbType.VarChar, 20), new SqlParameter(this.TO_LOT, SqlDbType.VarChar, 20), new SqlParameter(this.TO_LPN_ID, SqlDbType.VarChar, 20), new SqlParameter(this.CONDITION_CODE, SqlDbType.VarChar, 20), new SqlParameter(this.LOT_ATTR01, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR02, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR03, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR04, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR05, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR06, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR07, SqlDbType.VarChar, 50), 
                new SqlParameter(this.LOT_ATTR08, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR09, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR10, SqlDbType.VarChar, 50), new SqlParameter(this.PRODUCE_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.EXPIRY_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.RECEIPT_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.EXTERNAL_LOT, SqlDbType.VarChar, 100), new SqlParameter(this.CASE_CNT, SqlDbType.Decimal, 13), new SqlParameter(this.INNER_PACK, SqlDbType.Decimal, 13), new SqlParameter(this.PALLET, SqlDbType.Decimal, 13), new SqlParameter(this.CUBE, SqlDbType.Float, 8), new SqlParameter(this.GROSS_WGT, SqlDbType.Float, 8), new SqlParameter(this.NET_WGT, SqlDbType.Float, 8), new SqlParameter(this.LENGTH, SqlDbType.Float, 8), new SqlParameter(this.WIDTH, SqlDbType.Float, 8), new SqlParameter(this.HEIGHT, SqlDbType.Float, 8), 
                new SqlParameter(this.OTHER_UNIT1, SqlDbType.Float, 8), new SqlParameter(this.OTHER_UNIT2, SqlDbType.Float, 8), new SqlParameter(this.UNIT_PRICE, SqlDbType.Float, 8), new SqlParameter(this.EXTENDED_PRICE, SqlDbType.Float, 8), new SqlParameter(this.EFFECTIVE_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.UDF1, SqlDbType.VarChar, 30), new SqlParameter(this.UDF2, SqlDbType.VarChar, 30), new SqlParameter(this.UDF3, SqlDbType.VarChar, 30), new SqlParameter(this.UDF4, SqlDbType.VarChar, 30), new SqlParameter(this.UDF5, SqlDbType.VarChar, 30), new SqlParameter(this.UDF6, SqlDbType.VarChar, 30), new SqlParameter(this.UDF7, SqlDbType.VarChar, 30), new SqlParameter(this.UDF8, SqlDbType.VarChar, 30), new SqlParameter(this.UDF9, SqlDbType.VarChar, 30), new SqlParameter(this.UDF10, SqlDbType.VarChar, 30), new SqlParameter(this.REASON_CODE, SqlDbType.VarChar, 20), 
                new SqlParameter(this.PALLET_ID, SqlDbType.VarChar, 20), new SqlParameter(this.TYPE, SqlDbType.VarChar, 20), new SqlParameter(this.RETURN_TYPE, SqlDbType.VarChar, 20), new SqlParameter(this.RETURN_REASON, SqlDbType.VarChar, 20), new SqlParameter(this.DISPOSITION_TYPE, SqlDbType.VarChar, 20), new SqlParameter(this.DISPOSITION_CODE, SqlDbType.VarChar, 20), new SqlParameter(this.RETURN_CONDITION, SqlDbType.VarChar, 20), new SqlParameter(this.QC_REQUIRED, SqlDbType.VarChar, 1), new SqlParameter(this.QC_INSPECTED_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.QC_REJECTED_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.QC_REJ_REASON, SqlDbType.VarChar, 20), new SqlParameter(this.QC_STATUS, SqlDbType.VarChar, 1), new SqlParameter(this.QC_USER, SqlDbType.VarChar, 20), new SqlParameter(this.QC_AUTO_ADJUST, SqlDbType.VarChar, 1), new SqlParameter(this.RMA, SqlDbType.VarChar, 20), new SqlParameter(this.VENDOR_ID, SqlDbType.VarChar, 20), 
                new SqlParameter(this.VENDOR_NAME, SqlDbType.VarChar, 30), new SqlParameter(this.MATCH_LOTTABLE, SqlDbType.VarChar, 1), new SqlParameter(this.CROSS_DOCK_ID, SqlDbType.VarChar, 20), new SqlParameter(this.REMARK, SqlDbType.VarChar, 0x3e8), new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.DECLARATION_QTY, SqlDbType.Decimal, 13)
             };
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.WhID))
            {
                paramArray[0].Value = receiptDetailLpnInfo.WhID;
            }
            else
            {
                paramArray[0].Value = DBNull.Value;
            }
            paramArray[1].Value = receiptDetailLpnInfo.ReceiptID;
            paramArray[2].Value = receiptDetailLpnInfo.ReceiptLineID;
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.ExternReceiptID))
            {
                paramArray[3].Value = receiptDetailLpnInfo.ExternReceiptID;
            }
            else
            {
                paramArray[3].Value = DBNull.Value;
            }
            paramArray[4].Value = receiptDetailLpnInfo.ExternLineID;
            paramArray[5].Value = receiptDetailLpnInfo.OwnerID;
            paramArray[6].Value = receiptDetailLpnInfo.PoID;
            paramArray[7].Value = receiptDetailLpnInfo.PoLineID;
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.TariffID))
            {
                paramArray[8].Value = receiptDetailLpnInfo.TariffID;
            }
            else
            {
                paramArray[8].Value = DBNull.Value;
            }
            paramArray[9].Value = receiptDetailLpnInfo.SkuID;
            paramArray[10].Value = receiptDetailLpnInfo.AltSkuID;
            paramArray[11].Value = receiptDetailLpnInfo.LpnID;
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.CaseID))
            {
                paramArray[12].Value = receiptDetailLpnInfo.CaseID;
            }
            else
            {
                paramArray[12].Value = DBNull.Value;
            }
            paramArray[13].Value = receiptDetailLpnInfo.Status;
            DateTime CS00003 = new DateTime();
            if (receiptDetailLpnInfo.ReceivedDate != CS00003)
            {
                paramArray[14].Value = receiptDetailLpnInfo.ReceivedDate;
            }
            else
            {
                paramArray[14].Value = DBNull.Value;
            }
            paramArray[15].Value = receiptDetailLpnInfo.ExpectedQty;
            paramArray[0x10].Value = receiptDetailLpnInfo.AdjustedQty;
            paramArray[0x11].Value = receiptDetailLpnInfo.ReceivedQty;
            paramArray[0x12].Value = receiptDetailLpnInfo.RejectedQty;
            paramArray[0x13].Value = receiptDetailLpnInfo.UomID;
            paramArray[20].Value = receiptDetailLpnInfo.PackID;
            paramArray[0x15].Value = receiptDetailLpnInfo.ToLoc;
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.ToLot))
            {
                paramArray[0x16].Value = receiptDetailLpnInfo.ToLot;
            }
            else
            {
                paramArray[0x16].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.ToLpnID))
            {
                paramArray[0x17].Value = receiptDetailLpnInfo.ToLpnID;
            }
            else
            {
                paramArray[0x17].Value = DBNull.Value;
            }
            paramArray[0x18].Value = receiptDetailLpnInfo.ConditionCode;
            paramArray[0x19].Value = receiptDetailLpnInfo.ReceiptLot.LotAttr01;
            paramArray[0x1a].Value = receiptDetailLpnInfo.ReceiptLot.LotAttr02;
            paramArray[0x1b].Value = receiptDetailLpnInfo.ReceiptLot.LotAttr03;
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.ReceiptLot.LotAttr04))
            {
                paramArray[0x1c].Value = receiptDetailLpnInfo.ReceiptLot.LotAttr04;
            }
            else
            {
                paramArray[0x1c].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.ReceiptLot.LotAttr05))
            {
                paramArray[0x1d].Value = receiptDetailLpnInfo.ReceiptLot.LotAttr05;
            }
            else
            {
                paramArray[0x1d].Value = DBNull.Value;
            }
            paramArray[30].Value = receiptDetailLpnInfo.ReceiptLot.LotAttr06;
            paramArray[0x1f].Value = receiptDetailLpnInfo.ReceiptLot.LotAttr07;
            paramArray[0x20].Value = receiptDetailLpnInfo.ReceiptLot.LotAttr08;
            paramArray[0x21].Value = receiptDetailLpnInfo.ReceiptLot.LotAttr09;
            paramArray[0x22].Value = receiptDetailLpnInfo.ReceiptLot.LotAttr10;
            CS00003 = new DateTime();
            if (receiptDetailLpnInfo.ReceiptLot.ProduceDate != CS00003)
            {
                paramArray[0x23].Value = receiptDetailLpnInfo.ReceiptLot.ProduceDate;
            }
            else
            {
                paramArray[0x23].Value = DBNull.Value;
            }
            CS00003 = new DateTime();
            if (receiptDetailLpnInfo.ReceiptLot.ExpiryDate != CS00003)
            {
                paramArray[0x24].Value = receiptDetailLpnInfo.ReceiptLot.ExpiryDate;
            }
            else
            {
                paramArray[0x24].Value = DBNull.Value;
            }
            if (receiptDetailLpnInfo.ReceiptLot.ReceiptDate != new DateTime())
            {
                paramArray[0x25].Value = receiptDetailLpnInfo.ReceiptLot.ReceiptDate;
            }
            else
            {
                paramArray[0x25].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.ReceiptLot.ExternalLot))
            {
                paramArray[0x26].Value = receiptDetailLpnInfo.ReceiptLot.ExternalLot;
            }
            else
            {
                paramArray[0x26].Value = DBNull.Value;
            }
            paramArray[0x27].Value = receiptDetailLpnInfo.CaseCnt;
            paramArray[40].Value = receiptDetailLpnInfo.InnerPack;
            paramArray[0x29].Value = receiptDetailLpnInfo.Pallet;
            paramArray[0x2a].Value = receiptDetailLpnInfo.Cube;
            paramArray[0x2b].Value = receiptDetailLpnInfo.GrossWgt;
            paramArray[0x2c].Value = receiptDetailLpnInfo.NetWgt;
            paramArray[0x2d].Value = receiptDetailLpnInfo.Length;
            paramArray[0x2e].Value = receiptDetailLpnInfo.Width;
            paramArray[0x2f].Value = receiptDetailLpnInfo.Height;
            paramArray[0x30].Value = receiptDetailLpnInfo.OtherUnit1;
            paramArray[0x31].Value = receiptDetailLpnInfo.OtherUnit2;
            paramArray[50].Value = receiptDetailLpnInfo.UnitPrice;
            paramArray[0x33].Value = receiptDetailLpnInfo.ExtendedPrice;
            paramArray[0x34].Value = receiptDetailLpnInfo.EffectiveDate;
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.Udf1))
            {
                paramArray[0x35].Value = receiptDetailLpnInfo.Udf1;
            }
            else
            {
                paramArray[0x35].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.Udf2))
            {
                paramArray[0x36].Value = receiptDetailLpnInfo.Udf2;
            }
            else
            {
                paramArray[0x36].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.Udf3))
            {
                paramArray[0x37].Value = receiptDetailLpnInfo.Udf3;
            }
            else
            {
                paramArray[0x37].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.Udf4))
            {
                paramArray[0x38].Value = receiptDetailLpnInfo.Udf4;
            }
            else
            {
                paramArray[0x38].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.Udf5))
            {
                paramArray[0x39].Value = receiptDetailLpnInfo.Udf5;
            }
            else
            {
                paramArray[0x39].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.Udf6))
            {
                paramArray[0x3a].Value = receiptDetailLpnInfo.Udf6;
            }
            else
            {
                paramArray[0x3a].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.Udf7))
            {
                paramArray[0x3b].Value = receiptDetailLpnInfo.Udf7;
            }
            else
            {
                paramArray[0x3b].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.Udf8))
            {
                paramArray[60].Value = receiptDetailLpnInfo.Udf8;
            }
            else
            {
                paramArray[60].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.Udf9))
            {
                paramArray[0x3d].Value = receiptDetailLpnInfo.Udf9;
            }
            else
            {
                paramArray[0x3d].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.Udf10))
            {
                paramArray[0x3e].Value = receiptDetailLpnInfo.Udf10;
            }
            else
            {
                paramArray[0x3e].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.ReasonCode))
            {
                paramArray[0x3f].Value = receiptDetailLpnInfo.ReasonCode;
            }
            else
            {
                paramArray[0x3f].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.PalletID))
            {
                paramArray[0x40].Value = receiptDetailLpnInfo.PalletID;
            }
            else
            {
                paramArray[0x40].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.Type))
            {
                paramArray[0x41].Value = receiptDetailLpnInfo.Type;
            }
            else
            {
                paramArray[0x41].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.ReturnType))
            {
                paramArray[0x42].Value = receiptDetailLpnInfo.ReturnType;
            }
            else
            {
                paramArray[0x42].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.ReturnReason))
            {
                paramArray[0x43].Value = receiptDetailLpnInfo.ReturnReason;
            }
            else
            {
                paramArray[0x43].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.DispositionType))
            {
                paramArray[0x44].Value = receiptDetailLpnInfo.DispositionType;
            }
            else
            {
                paramArray[0x44].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.DispositionCode))
            {
                paramArray[0x45].Value = receiptDetailLpnInfo.DispositionCode;
            }
            else
            {
                paramArray[0x45].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.ReturnCondition))
            {
                paramArray[70].Value = receiptDetailLpnInfo.ReturnCondition;
            }
            else
            {
                paramArray[70].Value = DBNull.Value;
            }
            paramArray[0x47].Value = receiptDetailLpnInfo.QcRequired;
            paramArray[0x48].Value = receiptDetailLpnInfo.QcInspectedQty;
            paramArray[0x49].Value = receiptDetailLpnInfo.QcRejectedQty;
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.QcRejReason))
            {
                paramArray[0x4a].Value = receiptDetailLpnInfo.QcRejReason;
            }
            else
            {
                paramArray[0x4a].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.QcStatus))
            {
                paramArray[0x4b].Value = receiptDetailLpnInfo.QcStatus;
            }
            else
            {
                paramArray[0x4b].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.QcUser))
            {
                paramArray[0x4c].Value = receiptDetailLpnInfo.QcUser;
            }
            else
            {
                paramArray[0x4c].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.QcAutoAdjust))
            {
                paramArray[0x4d].Value = receiptDetailLpnInfo.QcAutoAdjust;
            }
            else
            {
                paramArray[0x4d].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.Rma))
            {
                paramArray[0x4e].Value = receiptDetailLpnInfo.Rma;
            }
            else
            {
                paramArray[0x4e].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.VendorID))
            {
                paramArray[0x4f].Value = receiptDetailLpnInfo.VendorID;
            }
            else
            {
                paramArray[0x4f].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.VendorName))
            {
                paramArray[80].Value = receiptDetailLpnInfo.VendorName;
            }
            else
            {
                paramArray[80].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.MatchLottable))
            {
                paramArray[0x51].Value = receiptDetailLpnInfo.MatchLottable;
            }
            else
            {
                paramArray[0x51].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.CrossDockID))
            {
                paramArray[0x52].Value = receiptDetailLpnInfo.CrossDockID;
            }
            else
            {
                paramArray[0x52].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailLpnInfo.Remark))
            {
                paramArray[0x53].Value = receiptDetailLpnInfo.Remark;
            }
            else
            {
                paramArray[0x53].Value = DBNull.Value;
            }
            paramArray[0x54].Value = receiptDetailLpnInfo.CreatedBy;
            paramArray[0x55].Value = receiptDetailLpnInfo.UpdatedBy;
            paramArray[0x56].Value = receiptDetailLpnInfo.DeclarationQty;
            return paramArray;
        }

        public int UpdateReceiptDeclarationQtyByReceiptIDLineID(DataBase dataBase, DbTransaction tran, string receiptID, string lineID, decimal DifferencesQty, string type, string updateBy, string whLoginID)
        {
            int result = 0;
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("  UPDATE @_@RECEIPT_DETAIL_LPN ");
                if (type == "DELETE")
                {
                    strSql.Append(" SET DECLARATION_QTY =DECLARATION_QTY- @DECLARATION_QTY,  ");
                }
                else
                {
                    strSql.Append(" SET DECLARATION_QTY = ISNULL(DECLARATION_QTY,0)+ @DECLARATION_QTY,");
                }
                strSql.Append(" UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE RECEIPT_ID = @RECEIPT_ID AND RECEIPT_LINE_ID=@RECEIPT_LINE_ID");
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DECLARATION_QTY", DifferencesQty), new SqlParameter("@UPDATED_BY", updateBy), new SqlParameter("@RECEIPT_ID", receiptID), new SqlParameter("@RECEIPT_LINE_ID", lineID) };
                result = dataBase.ExecuteNonQuery(tran, CommandType.Text, strSql.Replace("@_@", whLoginID).ToString(), paras);
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateReceiptDetailLpn(ReceiptDetailInfo receiptDetailLpnInfo, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                receiptDetailLpnInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_ReceiptDetailLpn_Parameters(receiptDetailLpnInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_RECEIPT_DETAIL_LPN.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int UpdateReceiptDetailLpn(DataBase dataBase, DbTransaction tran, ReceiptDetailInfo receiptDetailLpnInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                receiptDetailLpnInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_ReceiptDetailLpn_Parameters(receiptDetailLpnInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_RECEIPT_DETAIL_LPN.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateReceiptDetailLpnStatus(string receiptID, string status, string updatedBy, string whLoginID)
        {
            int result = 0;
            try
            {
                string sql = this.SQL_UPDATE_RECEIPT_DETAIL_LPN_STAUTS.Replace("@_@", whLoginID);
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@RECEIPT_ID", receiptID) };
                result = DBHelper.ExecuteNonQuery(CommandType.Text, sql, paras);
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateReceiptDetailLpnStatus(DataBase dataBase, DbTransaction tran, string receiptID, string status, string updatedBy, string whLoginID)
        {
            int result = 0;
            try
            {
                string sql = this.SQL_UPDATE_RECEIPT_DETAIL_LPN_STAUTS.Replace("@_@", whLoginID);
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@RECEIPT_ID", receiptID) };
                result = dataBase.ExecuteNonQuery(tran, CommandType.Text, sql, paras);
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateReceiptDetailStatus4Receiving(ReceiptDetailInfo receiptdetailInfo, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                string sql = this.SQL_UPDATE_RECEIPT_DETAIL_FOR_RECEIGING.Replace("@_@", whLoginID);
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIVED_QTY", receiptdetailInfo.ReceivedQty), new SqlParameter("@RECEIVED_DATE", receiptdetailInfo.ReceivedDate), new SqlParameter("@RECEIPT_DATE", receiptdetailInfo.ReceiptLot.ReceiptDate), new SqlParameter("@STATUS", receiptdetailInfo.Status), new SqlParameter("@LPN_ID", receiptdetailInfo.LpnID), new SqlParameter("@TO_LOC", receiptdetailInfo.ToLoc), new SqlParameter("@TO_LOT", receiptdetailInfo.ToLot), new SqlParameter("@TO_LPN_ID", receiptdetailInfo.ToLpnID), new SqlParameter("@UPDATED_BY", receiptdetailInfo.UpdatedBy), new SqlParameter("@RECEIPT_ID", receiptdetailInfo.ReceiptID), new SqlParameter("@RECEIPT_LINE_ID", receiptdetailInfo.ReceiptLineID) };
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, sql, paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int UpdateReceiptDetailStatus4Receiving(DataBase dataBase, DbTransaction tran, ReceiptDetailInfo receiptdetailInfo, string whLoginID)
        {
            int result = 0;
            string sql = this.SQL_UPDATE_RECEIPT_DETAIL_FOR_RECEIGING.Replace("@_@", whLoginID);
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIVED_QTY", receiptdetailInfo.ReceivedQty), new SqlParameter("@RECEIVED_DATE", receiptdetailInfo.ReceivedDate), new SqlParameter("@RECEIPT_DATE", receiptdetailInfo.ReceiptLot.ReceiptDate), new SqlParameter("@STATUS", receiptdetailInfo.Status), new SqlParameter("@LPN_ID", receiptdetailInfo.LpnID), new SqlParameter("@TO_LOC", receiptdetailInfo.ToLoc), new SqlParameter("@TO_LOT", receiptdetailInfo.ToLot), new SqlParameter("@TO_LPN_ID", receiptdetailInfo.ToLpnID), new SqlParameter("@UPDATED_BY", receiptdetailInfo.UpdatedBy), new SqlParameter("@RECEIPT_ID", receiptdetailInfo.ReceiptID), new SqlParameter("@RECEIPT_LINE_ID", receiptdetailInfo.ReceiptLineID) };
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, sql, paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }
    }
}

